Category: sql

SQL – DELETE JOIN Table of content The SQL DELETE… JOIN Clause DELETE… JOIN with WHERE Clause ”; Previous Next Simple deletion operation in SQL can be performed on a single record or multiple records of a table. And to delete records from multiple tables, the most straightforward approach would be to delete records from one table at a time. However, SQL makes it easier by allowing the deletion operation to be performed on multiple tables simultaneously. This is achieved using Joins. The SQL DELETE… JOIN Clause The purpose of Joins in SQL is to combine records of two or more tables based on common columns/fields. Once the tables are joined, performing the deletion operation on the obtained result-set will delete records from all the original tables at a time. For example, consider a database of an educational institution. It consists of various tables: Departments, StudentDetails, LibraryPasses, LaboratoryPasses etc. When a set of students are graduated, all their details from the organizational tables need to be removed, as they are unwanted. However, removing the details separately from multiple tables can be cumbersome. To make it simpler, we will first retrieve the combined data of all graduated students from all the tables using Joins; then, this joined data is deleted from all the tables using DELETE statement. This entire process can be done in one single query. Syntax Following is the basic syntax of the SQL DELETE… JOIN statement − DELETE table(s) FROM table1 JOIN table2 ON table1.common_field = table2.common_field; When we say JOIN here, we can use any type of Join: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc. Example To demonstrate this deletion operation, we must first create tables and insert values into them. We can create these tables using CREATE TABLE queries as shown below. Create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. Using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Following DELETE… JOIN query removes records from these tables at once − DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID; Output The output will be displayed in SQL as follows − Query OK, 3 rows affected (0.01 sec) Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Since, we only deleted records from CUSTOMERS table, the changes will not be reflected in the ORDERS table. We can verify it using the following query. SELECT * FROM ORDERS; The ORDERS table is displayed as − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 DELETE… JOIN with WHERE Clause The ON clause in DELETE… JOIN query is used to apply constraints on the records. In addition to it, we can also use the WHERE clause to make the filtration stricter. Observe the query below. Here,

SQL – Composite Key Table of content The SQL Composite Key Dropping a Composite Key in MySQL Dropping a Composite Key in SQL Server ”; Previous Next The SQL Composite Key An SQL Composite Key is a key that can be defined on two or more columns in a table to uniquely identify any record. It can also be described as a Primary Key created on multiple columns. Composite Keys are necessary in scenarios where a database table does not have a single column that can uniquely identify each row from the table. In such cases, we might need to use the combination of columns to ensure that each record in the table is distinct and identifiable. Let us understand the composite keys with an example. Suppose if we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below − We can select the two columns AADHAAR_ID and MOBILE_NO and define a Composite key on them, and it can be used to fetch the records of the CUSTOMERS table uniquely. Features of Composite Keys Following are some important features of the SQL Composite Key − A Composite Key can be created by combining more than one Candidate Key. Each Candidate Key (or column) that makes up a Composite Key may or may not be a Foreign Key. However, if all the columns of the Composite Key are Foreign Keys in their own right, then the Composite Key is known as a Compound Key. A Composite Key cannot be NULL; i.e. any column of the Composite Key must not contain NULL values. The individual columns making up the Composite Key can contain duplicate values, but, the combination of these columns must be unique across the database table. Syntax Following is the syntax to create an SQL Composite Key while creating a table − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, CONSTRAINT composite_key_name, PRIMARY KEY(column_name) ); Here, the composite_key_name is the optional placeholder which holds the name of a Composite Key in a table. It is used while dropping the constraint from a table in some databases. Example In the following example, we are creating a table named CUSTOMERS with multiple columns. The Composite Key is created when a PRIMARY KEY is defined on ID and NAME columns together. Look at the query below − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), CONSTRAINT ck_customers PRIMARY KEY (ID, NAME) ); Where, ck_customers is the name of a composite key of this table. Output Following is the output of the above statement − Query OK, 0 rows affected (0.02 sec) Verification As we have created a Composite Key on the columns ID and NAME of the CUSTOMERS table, the combination of values in these columns can not be duplicated. To verify it, let us insert two records with same values in these columns into the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (1, ”Ramesh”, 25, ”Delhi”, 1500.00 ); You can observe that the second INSERT statement generates an error message saying “Duplicate entry” as shown below − ERROR 1062 (23000): Duplicate entry ”1-Ramesh” for key ”customers.PRIMARY” Dropping a Composite Key in MySQL You can drop the composite key from a table in MySQL database using the ALTER TABLE… DROP statement. Syntax Following is the syntax to drop the Composite Key in MySQL − ALTER TABLE table_name DROP PRIMARY KEY; Example Using the following SQL statement, we can drop the Composite Key constraint from the CUSTOMERS table − ALTER TABLE CUSTOMERS DROP PRIMARY KEY; Output The above SQL statement produces the following output − Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 Verification Since, we have dropped the composite from the CUSTOMERS table, so now you can insert the duplicate values in the columns ID and NAME. Let us insert two records with the same ID and NAME into the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 25, ”Delhi”, 1500.00 ), (1, ”Ramesh”, 23, ”Kota”, 2000.00 ); If you retrieve the contents the CUSTOMERS table you can find the records with same ID and NAME as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 1 Ramesh 25 Delhi 1500.00 1 Ramesh 23 Kota 2000.00 Dropping a Composite Key in SQL Server In SQL Server, we have a different syntax to drop a composite key of a table. The syntax is almost similar, but we just need to specify the composite key name in order to drop it, rather than the keyword PRIMARY KEY. Syntax Following is the syntax to drop a composite key in SQL Server − ALTER TABLE table_name DROP composite_key_name; Example Assuming that a composite key “ck_customers” is created on ID and NAME columns of the CUSTOMERS table, we will use the following query to drop it − ALTER TABLE CUSTOMERS DROP ck_customers; Output When we execute the above query, the composite key will be dropped. Commands completed successfully. Verification To verify whether we have removed the composite key from the CUSTOMERS table or not, insert duplicate values into the ID and NAME columns

SQL – Primary Key Table of content The SQL Primary Key Creating an SQL Primary Key Creating Primary Key on an Existing Column Dropping an SQL Primary Key ”; Previous Next The SQL Primary Key The SQL Primary Key is a column (or combination of columns) that uniquely identifies each record in a database table. The Primary Key also speeds up data access and is used to establish a relationship between tables. Even though a table can only have one Primary Key, it can be defined on one or more fields. When a primary key is created on multiple fields of a table, it is called a Composite Key. Let us say, you are developing an application called “Customer Management System” to handle all the customer data of a member-only resort. This data can include their personal details, assigned member IDs, other details of the membership they opted, etc. And in all the tables created within this database, the member ID is used to distinguish the customers from each other. So, this field will be the Primary Key. Following is the diagram of a CUSTOMERS table that holds the personal details of the customers. And as we can observe, the primary key is defined on the CUST_ID column. Using this primary key, we can retrieve a unique record of any customer. Points to Remember Here are some key points of the PRIMARY KEY − It contains only a unique value. It can not be null. One table can have only one Primary Key. A primary key length cannot be more than 900 bytes. Creating an SQL Primary Key While creating a table using the CREATE TABLE statement, you can add the primary key constraint on a particular column of the table just by to specifying the name of the column along with the keyword “PRIMARY KEY”. Syntax Following is the syntax to define a column of a table as a primary key − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY(column_name) ); Example In the following example, we are trying to create a table with the name CUSTOMERS with various fields in an SQL database. While creating the table, we will add the constraint “PRIMARY KEY” on the column named ID. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Output Following is the output of the above SQL statement − Query OK, 0 rows affected (0.03 sec) Verification As we know primary key value must be unique, so you can not insert the record with the same ID. Here, we will verify the constraint created on the ID column, by inserting records with duplicate ID values. First of all, let”s insert a record into the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (3, ”Kaushik”, 23, ”Kota”, 2000.00); Now, let”s insert one more record with same ID − INSERT INTO CUSTOMERS VALUES (3, ”Chaitali”, 25, ”Mumbai”, 6500.00); As we have mentioned above, if any field/column is defined as Primary Key in a database table, two records can not have the same value in that column/field. Therefore, the second insert statement generates the following error − ERROR 1062 (23000): Duplicate entry ”3” for key ”customers.PRIMARY” Similarly, a primary key column cannot contain null values. Here, using the INSERT statement we are passing a NULL value to the primary key column (ID). INSERT INTO CUSTOMERS VALUES (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00); This statement generates the following error − ERROR 1048 (23000): Column ”ID” cannot be null Creating Primary Key on an Existing Column We can also add the PRIMARY KEY constraint on an existing column of a table using the ALTER TABLE statement. Syntax Following is the syntax to create a primary constraint on existing columns of a table − ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name); Example In this example, we are adding the PRIMARY KEY constraint on the NAME column of the existing CUSTOMERS table − ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY(NAME); Output Following is the output of the above statement − Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 Dropping an SQL Primary Key If you can add a Primary Key Constraint to a column in the table, you can drop it as well. This is done by using the ALTER TABLE… DROP statement. Syntax Following is the syntax of the ALTER TABLE statement to can drop the Primary key constraints from the column of a table − ALTER TABLE table_name DROP PRIMARY KEY; Example Let us consider the CUSTOMERS table where we have created a primary key constraint on a column named ID. You can drop this constraint from the column ID by executing the following statement − ALTER TABLE CUSTOMERS DROP PRIMARY KEY; Output The above SQL query produces the following output − Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 Verification As we have dropped the Primary key from the column named ID, we can insert multiple records with the same ID. Following statement inserts four records with the same ID − INSERT INTO CUSTOMERS VALUES (3, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (3, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (3, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (3, ”Muffy”, 24, ”Indore”, 10000.00 ); If you verify the content of this

SQL – UNION Operator Table of content The SQL UNION Operator UNION on a Single Field UNION on Multiple Fields UNION with WHERE Clause UNION with Aliases ”; Previous Next The SQL UNION Operator The SQL UNION operator is used to combine data from multiple tables by eliminating duplicate rows (if any). To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria − The same number of columns selected with the same datatype. These columns must also be in the same order. They need not have same number of rows. Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables. The column names in the final result set will be based on the column names selected in the first SELECT statement. If you want to use a different name for a column in the final result set, you can use an alias in the SELECT statement. Syntax The basic syntax of a UNION operator is as follows − SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]; Here, the given condition could be any given expression based on your requirement. UNION on a Single Field If we want to use UNION to combine the result sets of two or more SELECT statements on a single field, we can simply include that field in the SELECT statement of each query. The UNION operator will automatically remove any duplicate values in the final result set. When using UNION on a single field, the column names in the result set will be determined by the column name in the first SELECT statement. Therefore, you may need to use an alias in the SELECT statement to ensure that the column name is meaningful for the final result set. Example Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following query inserts values into this table using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The CUSTOMERS table is as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, creating the second table ORDERS using CREATE TABLE statement as shown below − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE DATETIME NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT INT NOT NULL, PRIMARY KEY (OID) ); Following query inserts values into this table using the INSERT statement − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000), (100, ”2009-10-08 00:00:00”, 3, 1500), (101, ”2009-11-20 00:00:00”, 2, 1560), (103, ”2008-05-20 00:00:00”, 4, 2060); The ORDERS table is as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Using the following query, let us combine the SALARY and AMOUNT columns from CUSTOMERS and ORDERS table (since these columns have similar datatypes) − SELECT SALARY FROM CUSTOMERS UNION SELECT AMOUNT FROM ORDERS; Output Output of the above query is as follows − SALARY 2000.00 1500.00 6500.00 8500.00 4500.00 10000.00 3000.00 1560.00 2060.00 UNION on Multiple Fields When we use UNION on multiple fields, the number and order of the fields in each SELECT statement must match. Also, the data types of the fields in each SELECT statement must be compatible for the UNION to work correctly. If the data types are not compatible, you may need to use conversion functions such as CAST or CONVERT to ensure that the data types match. Example As the CUSTOMERS and ORDERS tables are not union-compatible individually, let us first join these two tables into a bigger table using Left Join and Right Join. The joined tables retrieved will have same number of columns with same datatypes, becoming union compatible. Now, these tables are combined using UNION query shown below − SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID UNION SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output This would produce the following result − ID NAME AMOUNT DATE 1 Ramesh NULL NULL 2 Khilan 1560 2009-11-20 00:00:00 3 Kaushik 3000 2009-10-08 00:00:00 3 Kaushik 1500 2009-10-08 00:00:00 4 Chaitali 2060 2008-05-20 00:00:00 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL UNION with WHERE Clause We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them. Syntax Following is the syntax for using the WHERE clause with UNION operator − SELECT column1, column2, column3 FROM table1 WHERE column1 = ”value1” UNION SELECT column1, column2, column3 FROM table2 WHERE column1 = ”value2”; Example In the following query, we are retrieving the id”s of the customers where id is greater than 5 and 2 from the ”CUSTOMERS” and ”ORDERS” tables respectively − SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5 UNION SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2; Output Following is the result produced − ID SALARY 6 4500.00 7 10000.00 3 3000.00 3 1500.00 4 2060.00 UNION with ORDER BY Clause When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements

SQL – UPDATE JOIN Table of content The SQL UPDATE… JOIN Clause UPDATE… JOIN with WHERE Clause The UPDATE… JOIN Clause in SQL Server ”; Previous Next To update the data entered in a single database table using SQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we need to use the UPDATE… JOIN clause. For instance, if a student changes their primary phone number and wishes to update it in their organizational database, the information needs to be modified in multiple tables like student records, laboratory records, canteen passes etc. Using the JOIN clause, you can combine all these tables into one, and then using UPDATE statement, you can update the student data in them simultaneously. The SQL UPDATE… JOIN Clause The UPDATE statement only modifies the data in a single table and JOINS in SQL are used to fetch the combination of rows from multiple tables, with respect to a matching field. If we want to update data in multiple tables, we can combine multiple tables into one using JOINS and then update them using UPDATE statement. This is also known as cross-table modification. Syntax Following is the basic syntax of the SQL UPDATE… JOIN statement − UPDATE table(s) JOIN table2 ON table1.join_column = table2.join_column SET table1.column1 = table2.new_value1, table1.column2 = table2.new_value2; Where, JOIN can be: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc. Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Following UPDATE… JOIN query increments the salary of customers by 1000 with respect to the inflation of their order amount by 500 − UPDATE CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000, ORDERS.AMOUNT = ORDERS.AMOUNT + 500; Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows − SELECT * FROM CUSTOMERS; The updated CUSTOMERS table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 2500.00 3 Kaushik 23 Kota 3000.00 4 Chaitali 25 Mumbai 7500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, check whether the ORDERS table is updated using the following SELECT statement − SELECT * FROM ORDERS; The updated ORDERS table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3500.00 100 2009-10-08 00:00:00 3 2000.00 101 2009-11-20 00:00:00 2 2060.00 103 2008-05-20 00:00:00 4 2560.00 UPDATE… JOIN with WHERE Clause While updating records from multiple tables, if we use the WHERE clause along with the UPDATE… JOIN statement we can filter the records to be updated (from the combined result set). Syntax The syntax of SQL UPDATE… JOIN with WHERE clause in MySQL database is as follows − UPDATE table(s) JOIN table2 ON column3 = column4 SET table1.column1 = value1, table1.column2 = value2, … WHERE condition; Example Now, let us execute the following query to increase the salary of customer whose id is 3 − UPDATE CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000 WHERE ORDERS.CUSTOMER_ID = 3; Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows. SELECT * FROM CUSTOMERS; As we can see in the table below, SALARY value of “Kaushik” is increased by 1000 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 3000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The UPDATE… JOIN Clause in SQL Server The SQL UPDATE… JOIN Clause also works in SQL Server database. But, the syntax of the query is slightly different from that of MySQL. However, the working of it is exactly the same as MySQL query. In MySQL, the UPDATE statement is followed by the JOIN clause and SET statements respectively. Whereas, in MS SQL Server the SET statement is followed by the JOIN clause. Syntax Following is the syntax of the UPDATE… JOIN in SQL Server − UPDATE tables(s) SET column1 = value1, column2 = value2, … FROM table1 JOIN table2 ON table1.join_column = table2.join_column; Example In this example, we will update values of the CUSTOMERS and ORDERS table that we created above; using the following

SQL – Unique Key Table of content The SQL Unique Key Creating SQL Unique Key Multiple Unique Keys Unique Key on an Existing Column Dropping an SQL Unique Key ”; Previous Next The SQL Unique Key The SQL Unique Key (or, Unique constraint) does not allow duplicate values in a column of a table. It prevents two records from having same values in a column. Unique Key is just an alternative to the Primary Key; as both Unique and Primary Key constraints ensure uniqueness in a column of the table. Suppose we have a table named CUSTOMERS to store the customer records in a Bank and if one of the column names is MOBILE_NO then, we can create a UNIQUE constraint on this column to prevent the entry of multiple records with the same mobile number. Features of Unique Keys Following is the list of some key features of the Unique Key in an SQL database − The unique key is similar to the primary key in a table, but it can accept NULL values, whereas the primary key does not. It accepts only one NULL value. It cannot have duplicate values. It can also be used as a foreign key in another table. A table can have more than one Unique column. Creating SQL Unique Key You can create a Unique Key on a database table using the UNIQUE keyword in SQL. While creating a database table, specify this SQL keyword along with the column (where this key needs to be defined on). Syntax Following is the syntax to create a UNIQUE key constraint on a column in a table − CREATE TABLE table_name( column1 datatype UNIQUE KEY, column2 datatype, ….. ….. columnN datatype ); Example Using the following SQL query, we are creating a table named CUSTOMERS with five fields ID, NAME, AGE, ADDRESS, and SALARY in it. Here, we are creating a Unique Key on the ID column. CREATE TABLE CUSTOMERS ( ID INT NOT NULL UNIQUE KEY, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Output Following is the output of the above SQL statement − Query OK, 0 rows affected (0.03 sec) Verification Since we have created a UNIQUE constraint on the column named ID, we cannot insert duplicate values in it. Let us verify by inserting the following records with duplicate ID values into the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (1, ”Khilan”, 25, ”Delhi”, 1500.00 ); On execution, following error is displayed proving that the UNIQUE constraint is indeed defined on the ID column − ERROR 1062 (23000): Duplicate entry ”1” for key ”customers.ID” Multiple Unique Keys We can create one or more Unique Keys on one or more columns in an SQL table. Syntax Following is the syntax to create unique key constraints on multiple columns in a table − CREATE TABLE table_name( column1 datatype UNIQUE KEY, column2 datatype UNIQUE KEY, ….. ….. columnN datatype ); Example Assume we have created a table with the name CUSTOMERS in the SQL database using CREATE TABLE statement. A Unique key is defined on columns ID and NAME using the UNIQUE keyword as shown below − CREATE TABLE BUYERS ( ID INT NOT NULL UNIQUE KEY, NAME VARCHAR(20) NOT NULL UNIQUE KEY, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Output Following is the output of the above SQL statement − Query OK, 0 rows affected (0.03 sec) Verification Since we have created a UNIQUE constraint on the column named ID and NAME, we cannot insert duplicate values in it. Let us verify by inserting duplicate records into the BUYERS table using the following INSERT statement − INSERT INTO BUYERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (1, ”Rajesh”, 25, ”Delhi”, 1500.00 ); Following error is displayed − ERROR 1062 (23000): Duplicate entry ”1” for key ”customers.ID” In the same way if you try to insert the another record with duplicate value for the column NAME as − INSERT INTO BUYERS VALUES (2, ”Ramesh”, 36, ”Chennai”, 1700.00 ); Following error is generated − ERROR 1062 (23000): Duplicate entry ”Ramesh” for key ”buyers.NAME” Unique Key on an Existing Column Until now, we have only seen how to define a Unique Key on a column while creating a new table. But, we can also add a unique key on an existing column of a table. This is done using the ALTER TABLE… ADD CONSTRAINT statement. Syntax Following is the syntax to create a unique constraint on existing columns of a table − ALTER TABLE table_name ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (column_name); Note − Here the UNIQUE_KEY_NAME is just the name of the UNIQUE KEY. It is optional to specify and is used to drop the constraint from the column in a table. Example In this example, we add a Unique Key on the ADDRESS column of the existing CUSTOMERS table − ALTER TABLE CUSTOMERS ADD CONSTRAINT UNIQUE_ADDRESS UNIQUE(ADDRESS); Output Following is the output of the above statement − Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 Dropping an SQL Unique Key If you have already created a unique key on a column, you can drop it whenever it is not needed. To drop the Unique Key from the column of a table, you need to use the ALTER TABLE statement. Syntax

SQL – Cross Join Table of content The SQL Cross Join Joining Multiple Tables with Cross Join ”; Previous Next The SQL Cross Join An SQL Cross Join is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables. That means, this join will combine each row of the first table with each row of second table (i.e. permutations). A Cartesian product, or a cross product, is the result achieved from multiplication of two sets. This is done by multiplying all the possible pairs from both the sets. The sample figure below illustrates the cross join in a simple manner. As you can see, we considered two table columns: Hair Style and Hair Type. Each of these columns contain some records that need to be matched. Hence, using cross join, we combine each record in the “Hair Style” column with all records in the “Hair Type” column. The resultant table obtained is considered as the Cartesian product or Joined table. Syntax Following is the basic syntax of the Cross Join query in SQL − SELECT column_name(s) FROM table1 CROSS JOIN table2; Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS; Output The resultant table is as follows − ID NAME AMOUNT DATE 2 Khilan 1500.00 2009-10-08 00:00:00 1 Ramesh 1560 2009-11-20 00:00:00 2 Khilan 1560 2009-11-20 00:00:00 1 Ramesh 1500.00 2009-10-08 00:00:00 Joining Multiple Tables with Cross Join We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables. Syntax Following is the syntax to join multiple tables using cross join in SQL − SELECT column_name(s) FROM table1 CROSS JOIN table2 CROSS JOIN table3 CROSS JOIN table4 …. …. …. CROSS JOIN tableN; Example Assume we have created another table named ORDER_RANGE using the following query − CREATE TABLE ORDER_RANGE ( SNO INT NOT NULL, ORDER_RANGE VARCHAR (20) NOT NULL ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO ORDER_RANGE VALUES (1, ”1-100”), (2, ”100-200”), (3, ”200-300”); The ORDER_RANGE table is created as follows − SNO ORDER_RANGE 1 1-100 2 100-200 3 200-300 Following query combines the three tables CUSTOMERS, ORDERS and ORDER_RANGE, using cross join − SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE FROM CUSTOMERS CROSS JOIN ORDERS CROSS JOIN ORDER_RANGE; Output The resultant table is given below − ID NAME AMOUNT DATE ORDER_RANGE 2 Khilan 1560 2009-11-20 00:00:00 1-100 1 Ramesh 1560 2009-11-20 00:00:00 1-100 2 Khilan 1500.00 2009-10-08 00:00:00 1-100 1 Ramesh 1500.00 2009-10-08 00:00:00 1-100 2 Khilan 1560 2009-11-20 00:00:00 100-200 1 Ramesh 1560 2009-11-20 00:00:00 100-200 2 Khilan 1500.00 2009-10-08 00:00:00 100-200 1 Ramesh 1500.00 2009-10-08 00:00:00 100-200 2 Khilan 1560 2009-11-20 00:00:00 200-300 1 Ramesh 1560 2009-11-20 00:00:00 200-300 2 Khilan 1500.00 2009-10-08 00:00:00 200-300 1 Ramesh 1500.00 2009-10-08 00:00:00 200-300 Print Page Previous Next Advertisements ”;

SQL – IS NOT NULL Table of content The SQL IS NOT NULL Operator IS NOT NULL with COUNT() Function IS NOT NULL with DELETE Statement IS NOT NULL with UPDATE Statement ”; Previous Next A NULL value indicates a missing or unknown value. It appears to be blank and does not contain any data. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. For checking null values we can use two basic operators. IS NULL IS NOT NULL The SQL IS NOT NULL Operator The SQL IS NOT NULL operator is used to filter data by verifying whether a particular column has a not-null values. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE. By using the IS NOT NULL operator, we can only fetch the records that contain valid data in a particular column. Syntax Following is the syntax of the SQL IS NOT NULL operator − SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Example Firstly, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20), AGE INT, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, NULL ), (2, ”Khilan”, 25, NULL, 1500.00 ), (3, ”Kaushik”, NULL, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, NULL ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, NULL, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, NULL, 10000.00 ); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 NULL 1500.00 3 Kaushik NULL Kota 2000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 8500.00 6 Komal NULL Hyderabad 4500.00 7 Muffy 24 NULL 10000.00 Example In the following query, we are going to return all the records from the CUSTOMERS table where the ADDRESS is not null − SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL; Output On executing the above query, it will generate the output as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 3 Kaushik NULL Kota 2000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 8500.00 6 Komal NULL Hyderabad 4500.00 IS NOT NULL with COUNT() Function We can use the IS NOT NULL operator along with the SQL COUNT() function to count only the non-null values in a specific column. Syntax Following is the syntax of IS NOT NULL operator with the COUNT() function − SELECT COUNT(column_name) FROM table_name WHERE condition IS NOT NULL; Example The following query returns the count of all rows in the CUSTOMERS table where the SALARY column is not null − SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NOT NULL; Output The output produced is as shown below − COUNT(*) 5 IS NOT NULL with DELETE Statement In SQL, we can delete all rows that do not contain NULL values in a specific column using the DELETE statement with IS NOT NULL operator. Syntax Following is the syntax of the IS NOT NULL operator with the DELETE statement in SQL − DELETE FROM table_name WHERE columnname1, columnname2, … IS NOT NULL; Example In the following query, we are deleting records which are not null in the SALARY column of the CUSTOMERS table − DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL; Output We get the following result − Query OK, 5 rows affected (0.02 sec) Verification Execute the SELECT query given below to check whether the table has been changed or not − SELECT * FROM CUSTOMERS; If we compile and run the program, the result is produced as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 4 Chaitali 25 Mumbai NULL IS NOT NULL with UPDATE Statement We can use the UPDATE statement with the IS NOT NULL operator in SQL to update records with not-null records in a particular column. Syntax Following is the syntax of the IS NOT NULL operator with the UPDATE statement in SQL − UPDATE table_name SET column1 = value1, column2 = value2, … WHERE columnname1, columnname2, … IS NOT NULL; Example Truncate the CUSTOMERS table and reinsert all the 7 records into it again. The following query, increments all the values in the SALARY column of the with 5000, where the salary value is not null − UPDATE CUSTOMERS SET SALARY = SALARY+5000 WHERE SALARY IS NOT NULL; Output When we execute the program above, the output is obtained as follows − Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 Verification To check whether the table has been updated or not, execute the SELECT query below − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 NULL 6500.00 3 Kaushik NULL Kota 7000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 13500.00 6 Komal NULL Hyderabad 9500.00 7 Muffy 24 NULL 15000.00 Print Page Previous Next Advertisements ”;

SQL – INTERSECT Table of content The SQL INTERSECT Operator INTERSECT with BETWEEN Operator INTERSECT with IN Operator INTERSECT with LIKE Operator ”; Previous Next In mathematical set theory, the intersection of two sets is a collection of values that are common to both sets. In real-time scenarios, there will be a huge number of tables in a database that contains information. The user may find it challenging to gather common information from various tables. So we use the INTERSECT operator to accomplish that. It helps to retrieve the common data from various tables. The SQL INTERSECT Operator The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two or more tables. Let us consider the below tables as an example to get a better understanding − If we perform the intersection operation on both tables described above using the INTERSECT operator, it returns the common records which are Dev and Aarohi. MySQL database does not support the INTERSECT operator. Instead of this, we can use the DISTINCT operator along with the INNER JOIN clause to retrieve common records from two or more tables. Syntax Following is the SQL syntax of INTERSECT operator in Microsoft SQL Server − SELECT column1, column2,…, columnN FROM table1, table2,…, tableN INTERSECT SELECT column1, column2,…, columnN FROM table1, table2,…, tableN There are some mandatory rules for INTERSECT operations such as the number of columns, data types, and other columns must be the same in both SELECT statements for the INTERSECT operator to work correctly. Example First of all, let us create a table named STUDENTS using the following query − CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) ); Let”s insert some values into the table using the following query − INSERT INTO STUDENTS VALUES (1, ”Naina”, ”Maths”, 24, ”Cricket”), (2, ”Varun”, ”Physics”, 26, ”Football”), (3, ”Dev”, ”Maths”, 23, ”Cricket”), (4, ”Priya”, ”Physics”, 25, ”Cricket”), (5, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (6, ”Kalyan”, ”Maths”, 30, ”Football”); The table produced is as shown below − ID NAME SUBJECT AGE HOBBY 1 Naina Mathematics 24 Cricket 2 Varun Physics 26 Football 3 Dev Mathematics 23 Cricket 4 Priya Physics 25 Cricket 5 Adithya Chemistry 21 Cricket 6 Kalyan Mathematics 30 Football Now, let us create another table named STUDENTS_HOBBY using the following query − CREATE TABLE STUDENTS_HOBBY( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) ); Once the table is created, let us insert some values to the table using the query below − INSERT INTO STUDENTS_HOBBY VALUES (1, ”Vijay”, ”Cricket”, 18), (2, ”Varun”, ”Football”, 26), (3, ”Surya”, ”Cricket”, 19), (4, ”Karthik”, ”Cricket”, 25), (5, ”Sunny”, ”Football”, 26), (6, ”Dev”, ”Cricket”, 23); The table created is as follows − ID NAME HOBBY AGE 1 Vijay Cricket 18 2 Varun Football 26 3 Surya Cricket 19 4 Karthik Cricket 25 5 Sunny Football 26 6 Dev Cricket 23 Now, we are retrieving the common records from both the tables using the following query − SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS; Output When we execute the above query, the output is obtained as follows − NAME AGE HOBBY Dev 23 Cricket Varun 26 Football INTERSECT with BETWEEN Operator We can use the INTERSECT operator with the BETWEEN operator in SQL to find records that fall within a specified range. Example Now, let us retrieve the name, age, and hobby of students aged between 25 and 30 from both the ”STUDENTS” and ”STUDENTS_HOBBY” tables, returning only the common rows within the specified age range − SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE AGE BETWEEN 25 AND 30 INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE AGE BETWEEN 20 AND 30; Output The output for the above query is produced as given below − NAME AGE HOBBY Varun 26 Football INTERSECT with IN Operator We can also use the INTERSECT operator with the IN operator in SQL to find the common records that exists in the specified list of values. The IN operator is used to filter a result set based on a list of specified values. Example The following SQL query returns the name, age, and hobby of students who have ”Cricket” as their hobby in both ”STUDENTS” and ”STUDENTS_HOBBY” tables − SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE HOBBY IN(”Cricket”) INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE HOBBY IN(”Cricket”); Output When we execute the above query, the output is obtained as follows − NAME AGE HOBBY Dev 23 Cricket INTERSECT with LIKE Operator The LIKE operator is used to perform pattern matching on a string. The INTERSECT operator can also be used with the LIKE operator in SQL to find the common rows that matches with the specified pattern. Example The query below retrieves the names that start with ”V” using the wildcard ”%” in the LIKE operator from the common names of both tables − SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE NAME LIKE ”v%” INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE NAME LIKE ”v%”; Output The output for the above query is produced as given below − NAME AGE HOBBY Varun 26 Football Print Page Previous Next Advertisements ”;

SQL – Inner Join Table of content The SQL Inner Join Joining Multiple Tables Using Inner Join Inner Join with WHERE Clause ”; Previous Next An SQL Join clause is used to combine multiple related tables in a database, based on common fields/columns. There are two major types of joins: Inner Join and Outer Join. Other joins like Left Join, Right Join, Full Join etc. Are just subtypes of these two major joins. In this tutorial, we will only learn about the Inner Join. The SQL Inner Join The SQL Inner Join is a type of join that combines multiple tables by retrieving records that have matching values in both tables (in the common column). It compares each row of the first table with each row of the second table, to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied, the column values from both tables are combined into a new table. The Inner Join is also referred as Equijoin. It is the default join; i.e., even if the “Join“keyword is used instead of “Inner Join“, tables are joined using matching records of common columns. Explanation Let us look at an example scenario to have a better understanding. Suppose we have the information of employees in a company divided between two tables namely EmpDetails and Marital status. Where, EmpDetails table holds details like Employee ID, Name and Salary. MaritalStatus table holds the details Employee ID, Age, and Marital Status. When we perform the Inner Join operation on these two tables based on the join-predicate EmpDetails.EmpID = MaritalStatus.EmpID, the resultant records hold the following info: ID, Name, Salary, Age and, Status of the matched records. Syntax Following is the basic syntax of SQL Inner Join − SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Let us now combine these two tables using the Inner Join query as shown below − SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The result of this query is obtained as follows − ID NAME AMOUNT DATE 3 Kaushik 3000.00 2009-10-08 00:00:00 3 Kaushik 1500.00 2009-10-08 00:00:00 2 Khilan 1560.00 2009-11-20 00:00:00 4 Chaitali 2060.00 2008-05-20 00:00:00 Joining Multiple Tables Using Inner Join Until now, we have only learnt how to join two tables using Inner Join. However, we can also join as many tables as possible, using Inner Join, by specifying the condition (with which these tables are to be joined). Syntax Following is the syntax to join more than two tables using Inner Join − SELECT column1, column2, column3… FROM table1 INNER JOIN table2 ON condition_1 INNER JOIN table3 ON condition_2 …. …. INNER JOIN tableN ON condition_N; Note that, even in this case, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined. Example Let us make use of the previous tables CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will create the EMPLOYEE table using the query below − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The details of EMPLOYEE table can be seen below. EID EMPLOYEE_NAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Using the following query, we can combine three tables CUSTOMERS, ORDERS and EMPLOYEE. SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID INNER JOIN EMPLOYEE ON ORDERS.OID = EMPLOYEE.EID; Output The result of the inner join query above is shown as follows − OID DATE AMOUNT EMPLOYEE_NAME 102 2009-10-08 00:00:00 3000.00 SARIKA 100 2009-10-08 00:00:00 1500.00 ALEKHYA 101 2009-11-20 00:00:00 1560.00 REVATHI 103 2008-05-20 00:00:00 2060.00 VIVEK Inner Join with WHERE Clause Clauses in SQL work with the purpose of applying constraints while retrieving data using SQL queries. There are various clauses that SQL uses to constraint the data; such as WHERE clause, GROUP BY clause, ORDER BY clause, UNION clause etc. The WHERE clause is used to filter the data from tables. This clause specifies a condition to retrieve only those records that satisfy it. Inner Join uses WHERE clause