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 −

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 −

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 −

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 −

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, we are deleting the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00.

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

Output

On executing the query, following output is displayed.

Query OK, 1 row 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 CUSTOMERS table after deletion is as follows −

Since we only deleted records from the 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 −

Self Join, as its name suggests, is a type of join that combines the records of a table with itself.

Suppose an organization, while organizing a Christmas party, is choosing a Secret Santa among its employees based on some colors. It is designed to be done by assigning one color to each of its employees and having them pick a color from the pool of various colors. In the end, they will become the Secret Santa of an employee this color is assigned to.

As we can see in the figure below, the information regarding the colors assigned and a color each employee picked is entered into a table. The table is joined to itself using self join over the color columns to match employees with their Secret Santa.

The SQL Self Join

The SQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, alias of the tables should be used at least once.

Self Join is a type of inner join, which is performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it.

Unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause.

Syntax

Following is the basic syntax of SQL Self Join −

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Here, the WHERE clause could be any given expression based on your requirement.

Example

Self Join only requires one table, so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn.

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 −

Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said Customers on the basis of their earnings. We are doing this with the help of the WHERE clause.

SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

Output

The resultant table displayed will list out all the customers that earn lesser than other customers −

Self Join with ORDER BY Clause

After joining a table with itself using self join, the records in the combined table can also be sorted in an order, using the ORDER BY clause.

Syntax

Following is the syntax for it −

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
ORDER BY column_name;

Example

Let us join the CUSTOMERS table with itself using self join on a WHERE clause; then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column, as shown in the following query.

SELECT  a.ID, b.NAME as EARNS_HIGHER, a.NAME
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY
ORDER BY a.SALARY;

Output

The resultant table is displayed as follows −

Not just the salary column, the records can be sorted based on the alphabetical order of names, numerical order of Customer IDs etc.

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 −

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 −

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 −

Now, check whether the ORDERS table is updated using the following SELECT statement −

SELECT * FROM ORDERS;

The updated ORDERS table is displayed as follows −

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 −

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 UPDATE… JOIN query −

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

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 −

SQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.

Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.

Working of UNION

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.

The tables are said to be union compatible if they follow the conditions given below −

• The tables to be combined must have same number of columns with the same datatype.
• The number of rows need not be same.

Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.

Note − Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.

Syntax

Following is the syntax of the SQL UNION operator −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

Create table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''PHOTOGRAPHY'');

Now, let us combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is −

Working of JOIN

The Join operation is used to combine information from multiple related tables into one, based on their common fields. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.

There are two types of Joins −

• Inner Join
• Outer Join

The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join.

The result table of the Outer join includes both matched and unmatched rows from the first table. It is divided into subtypes like Left Join, Right Join, and Full Join.

Syntax

Following is the basic syntax of a Join operation in SQL −

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example

In the following example, we will join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below –

SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, COURSES_PICKED
FROM COURSES_PICKED c
JOIN EXTRA_COURSES_PICKED e
ON c.STUDENT_ID = e.STUDENT_ID;

Output

The resultant table will be displayed as follows −

UNION Vs JOIN

As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.

Let us summarize all the difference between these queries below −

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

Following is the SQL query to drop the UNIQUE constraint from the column of a table −

ALTER TABLE table_name DROP CONSTRAINT UNIQUE_KEY_NAME;

Example

Consider the CUSTOMERS table created above, we have created the UNIQUE constraints on three columns named ID, NAME and ADDRESS; drop the UNIQUE constraints from the column ADDRESS by executing the following SQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_ADDRESS;

Output

Following is the output of the above statement −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us insert two duplicate records of column ADDRESS −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Ahmedabad'', 1500.00 );

If you verify the contents of the table, you can observe that both the records have the same ADDRESS as shown below −

UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables.

However, for these operators to work on these tables, they need to follow the conditions given below −

• The tables to be combined must have the same number of columns with the same datatype.
• The number of rows need not be the same.

Once these criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table.

Column names of first table will become column names of the resultant table, and contents of second table will be merged into resultant columns of same data type.

What is UNION?

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It just combines the information from multiple tables that are union compatible.

Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records.

Syntax

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two tables COURSES_PICKED and EXTRA_COURSES_PICKED with the same number of columns having the same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

The table will be displayed as shown below −

Now, let us create another table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the “EXTRA_COURSES_PICKED” table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''MATHEMATICS'');

The table will be created as shown below −

Now, let us combine both of these tables using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is as follows −

What is UNION ALL?

UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one table. However, this operator also preserves the duplicate rows in the resultant tables.

Suppose there are two tables, one of which contains the number of games a player competed in internationally and the other contains the number of games a player played nationally.

As we can see in the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases.

Syntax

Following is the syntax of UNION ALL operator in SQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

In the following example, let us perform UNION ALL operation on the same sample tables given above: “COURSES_PICKED” and “EXTRA_COURSES_PICKED”, using the given query below −

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table is displayed as follows −

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 −

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 −

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 −

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 −

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 −

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 −

The SQL EXCEPT Operator

The EXCEPT operator in SQL is used to retrieve all the unique records from the left operand (query), except the records that are present in the result set of the right operand (query).

In other words, this operator compares the distinct values of the left query with the result set of the right query. If a value from the left query is found in the result set of the right query, it is excluded from the final result.

For better understanding consider two tables with records as shown in the following image −

If we perform the EXCEPT operator on the above two tables to retrieve the names, it will display the distinct records only from the first table which are not in common with the records of the second table.

Here, “Dev” is common in both tables. So, the EXECPT operator will eliminate it and retrieves only “Sara” and “Jay” as output.

MySQL database does not support the EXCEPT operator. Instead of this, we can use the DISTINCT keyword along with the LEFT JOIN clause to retrieve distinct values from the left table.

Syntax

Following is the SQL syntax of the EXCEPT operator in Microsoft SQL server −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional
EXCEPT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional

The number and order of columns in both SELECT statements should be the same.

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''),
(7, ''Aditya'', ''Chemistry'', 21, ''Cricket''),
(8, ''Kalyan'', ''Chemistry'', 32, ''Cricket'');

The table produced is as shown below −

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 −

Now, let us perform the except operation on the above two tables −

SELECT NAME, HOBBY, AGE FROM STUDENTS
EXCEPT
SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY;

Output

Output of the above query is as shown below −

EXCEPT with BETWEEN Operator

We can use the EXCEPT operator with the BETWEEN operator in SQL to exclude records that fall within a specified range.

Example

In the following SQL query, we are retrieving the records of students aged between 20 and 30 from the STUDENTS table, excluding those who are also aged between 20 and 30 from the STUDENTS_HOBBY table −

SELECT NAME, HOBBY, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30
EXCEPT
SELECT NAME, HOBBY, AGE
FROM STUDENTS_HOBBY
WHERE AGE BETWEEN 20 AND 30

Output

When we execute the program query, the output is obtained as follows −

Except with IN Operator

The IN operator is used to filter a result set based on a list of specified values. We can also use the EXCEPT operator with the IN operator in SQL to exclude records that matches values in the specified list.

Example

Here, we are retrieving the records of students with Cricket as a hobby, from the STUDENTS table, excluding those who also have Cricket as hobby from the STUDENTS_HOBBY table −

SELECT NAME, HOBBY, AGE FROM STUDENTS
WHERE HOBBY IN(''Cricket'')
EXCEPT
SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY
WHERE HOBBY IN(''Cricket'')

Output

Following is the output of the above query −

EXCEPT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string. The EXCEPT operator can also be used with the LIKE operator in SQL to exclude rows that matches with the specified pattern.

Example

In here, we are retrieving records from the STUDENTS table where the values in the HOBBY column starts with ”F”, while excluding similar rows from the STUDENTS_HOBBY table −

SELECT ID, NAME, HOBBY, AGE FROM STUDENTS
WHERE HOBBY LIKE ''F%''
EXCEPT
SELECT ID, NAME, HOBBY, AGE FROM STUDENTS_HOBBY
WHERE HOBBY LIKE ''F%

Output

The output for the above query is produced as given below −

The SQL Join Clause

The SQL Join clause is used to combine data from two or more tables in a database. When the related data is stored across multiple tables, joins help you to retrieve records combining the fields from these tables using their foreign keys.

The part of the Join clause that specifies the columns on which records from two or more tables are joined is known as join-predicate. This predicate is usually specified along with the ON clause and uses various comparison operators such as, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect multiple join predicates with logical operators AND, OR, and NOT.

We can use JOINs along with and , SQL queries to update and delete records from across multiple tables. When you retrieve a table using joins, the resultant table displayed is not stored anywhere in the database.

Syntax

Following is the basic syntax of a the SQL JOIN CLAUSE −

SELECT column_name(s)
FROM table1
JOIN table2;

Example

Assume we have created a CUSTOMERS table that contains details of the customers of an organization 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 CUSTOMERS table will be created as follows −

Following is another table ORDERS which contains the order details made by the customers.

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 ORDERS table will be created as follows −

Following query performs the join operation on the tables CUSTMERS and ORDERS −

SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

By executing the query above, the resultant table is displayed and contains the values present in ID, NAME, AGE fields of CUSTOMERS table and AMOUNT field of ORDERS table.

Types of joins in SQL

SQL provides various types of Joins that are categorized based on the way data across multiple tables are joined together. They are listed as follows −

Inner Join

An is the default join which retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together.

Outer Join

An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, unlike Inner Join. Outer join is further divided into three subtypes – Left Join, Right Join and Full Join.

Following are the different types of outer Joins −

• − returns all rows from the left table, even if there are no matches in the right table.

• − returns all rows from the right table, even if there are no matches in the left table.

• − returns rows when there is a match in one of the tables.

Other Joins

In addition to these there are two more joins −

• − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

• − returns the Cartesian product of the sets of records from the two or more joined tables.

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 −

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 −

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 −

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.

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 −

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 to apply more constraints on the data to be retrieved. For instance, while retrieving the employee records of an organization, if we only want to check the data of employees that earn more than 25000 in a month, we need to specify a WHERE condition (salary > 25000) to retrieve only those employee records.

Syntax

The syntax of Inner Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example

In this example we are joining the tables CUSTOMERS and ORDERS using the inner join query and we are applying some constraints on the result using the WHERE clause.

Here, we are retrieving the ID and NAME from the CUSTOMERS table and DATE and AMOUNT from the ORDERS table where the amount paid is higher than 2000.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The resultant table after applying the where clause with inner join contains the rows that has AMOUNT values greater than 2000.00 −