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 −

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.

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 −

The SQL Full Join

SQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fills NULL values for missing matches on either side. In short, full join is a type of outer join that combines the result-sets of both left and right joins.

MySQL does not support Full Outer Join. Instead, you can imitate its working by performing union operation between the result-sets obtained from Left Join and Right Join.

Let us understand this concept in detail with the help of a Venn diagram below. Assume that we have two tables as two sets (represented by circles). The result-set (or newly joined table) obtained using full join is nothing but the union of these two sets.

You can also achieve the equivalent result-set of FULL JOIN by performing the UNION operation on result-sets of the LEFT JOIN and RIGHT JOIN.

Syntax

Following is the basic syntax of Full Join in SQL −

SELECT column_name(s)
FROM table1
FULL 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 −

Following query joins the two tables CUSTOMERS and ORDERS in SQL Server −

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

Output

The resultant table is produced as follows −

Joining Multiple Tables with Full Join

The Full Join query can also be used to join more than just two tables. To do that, we sequentially combine two tables at a time, until all the tables are joined together.

Note that in MySQL database, there is no provision to directly use the FULL JOIN keyword to perform join operation on multiple tables. Instead, calculate the UNION of LEFT JOIN and RIGHT JOIN on two tables at a time, until all the tables are joined.

Syntax

The syntax to join multiple tables using Full Join is given below −

SELECT column1, column2, column3...
FROM table1
FULL JOIN table2
ON condition_1
FULL JOIN table3
ON condition_2
....
....
FULL JOIN tableN
ON condition_N;

Example

To demonstrate Full Join, let us consider the sample tables CUSTOMERS and ORDERS that we previously created, and create another table name EMPLOYEE using the following query −

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 EMPLOYEE table created, will be as shown below −

Let us join these three tables using the full join query given below −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
FULL JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

The resultant table is obtained as follows −

Full Join with WHERE Clause

Joins use the ON clause to filter records by default. Let us suppose there is a further requirement to filter these records based on a certain condition/constraint, we can also make use of the WHERE clause with Joins.

Syntax

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

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

Example

Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.

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

Output

The resultant table after applying the WHERE clause with full join contains the rows that has amount values greater than 2000.00 −

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 −

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 −

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 −

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 −

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 −

SQL Joins are used to retrieve records from multiple tables based on a given condition. A Join includes the records that satisfy the given condition and outer join results a table that contains both matched and unmatched rows.

Left Outer Join, as discussed in the previous tutorial, is used to find the union of two tables with respect to the left table. In this tutorial, let us discuss about the Right outer join.

The SQL Right Join

The Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

If the ON clause matches zero records in the left table; the join will still return a row in the result, but with a NULL value in each column of the left table.

Syntax

Following is the basic syntax of Right Join in SQL −

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

The tables we are using in this example are named CUSTOMERS and ORDERS.

Assume we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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 −

Now, let us join these two tables using the Right Join query as follows −

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

Output

This would produce the following result −

Joining Multiple Tables with Right Join

Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first.

In addition, the rows of first table are matched with the rows in second table. If the records are not matched and the number of records in the second table is greater than the first, NULL is returned as the values in first table.

Syntax

Following is the syntax to join multiple tables using Right Join −

SELECT column1, column2, column3...
FROM table1
RIGHT JOIN table2
ON condition_1
RIGHT JOIN table3
ON condition_2
....
....
RIGHT JOIN tableN
ON condition_N;

Example

Here, let us consider the previously created tables CUSTOMERS and ORDERS; and create a new table named EMPLOYEE using the following query −

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 −

Following query joins these three tables using the Right Join query −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME,
ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
RIGHT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

The resultant table is obtained as follows −

Right Join with WHERE Clause

A WHERE Clause is used to filter out records that satisfy the condition specified by it. This clause can be used with the Right Join query to apply certain filters on the joined result-set.

Syntax

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

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

Example

Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the right join query by applying some constraints using the WHERE clause.

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

Output

The resultant table after applying the where clause with right join contains the rows that has amount values greater than 1000.00 −

You can rename a table or a column in a database temporarily by giving them another pseudo name. This pseudo name is known as Alias. The use of aliases is to address a specific table or a column in an SQL statement without changing their original name in the database. Aliases are created with the AS keyword.

Aliases can be especially useful when working with complex queries involving multiple tables or columns with similar names. By assigning temporary names to these tables or columns, you can make your SQL query more readable and easier to understand.

The SQL Aliasing

Aliases are used to address database tables with a shorter or more meaningful name within an SQL query. The basic syntax of a table alias is as follows.

SELECT column1, column2....
FROM table_name AS alias_name;

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 obtained is as follows −

Now, we are creating the second table ORDERS using CREATE TABLE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATES 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 obtained is as shown below −

Now, the following query shows the usage of a table alias. The CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” −

SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE  C.ID = O.CUSTOMER_ID;

Output

This would produce the following result −

Aliasing Column Names

We can also use an alias for a column name in SQL to give it a different name in the result set of a query. The basic syntax of a column alias is as follows −

SELECT column_name AS alias_name
FROM table_name;

Example

Following is the usage of a column alias. Here, the NAME column is aliased as ”CUSTOMER_NAME” −

SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
FROM CUSTOMERS;

Output

This would produce the following result −

Aliasing with Self Join

The SQL Self Join is used to join a table to itself as if the table were two tables. During this process, we need to use alias for one of the tables with a temporary name to avoid misunderstandings. This renaming is done using aliases.

Syntax

Following is the syntax for performing a self-join with aliases −

SELECT column_name(s)
FROM my_table a, my_table b
ON a.join_column = b.join_column;

Example

Now, let us join the CUSTOMERS table to itself using the following Self Join query. Our aim is to establish a relationship among customers on the basis of their earnings. In here, we are using aliases with column names as well as with the table names −

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
Output
Output of the above query is as follows −


ID
EARNS_HIGHER
EARNS_LESS
LOWER_SALARY


2
Ramesh
Khilan
1500.00


2
Kaushik
Khilan
1500.00


6
Chaitali
Komal
4500.00


3
Chaitali
Kaushik
2000.00


2
Chaitali
Khilan
1500.00


1
Chaitali
Ramesh
2000.00


6
Hardik
Komal
4500.00


4
Hardik
Chaitali
6500.00


3
Hardik
Kaushik
2000.00


2
Hardik
Khilan
1500.00


1
Hardik
Ramesh
2000.00


3
Komal
Kaushik
2000.00


2
Komal
Khilan
1500.00


1
Komal
Ramesh
2000.00


6
Muffy
Komal
4500.00


5
Muffy
Hardik
8500.00


4
Muffy
Chaitali
6500.00


3
Muffy
Kaushik
2000.00


2
Muffy
Khilan
1500.00


1
Muffy
Ramesh
2000.00











		

	

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 −

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.