Search Contents

Monday, 14 November 2016

JOIN in SQL

What is SQL JOIN?


In SQL, when we have to combine records from two or more tables we use the JOIN clause. A JOIN can be used to combine field from two or more tables that have same values.

An example of JOIN is:
SQL> SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The JOIN is always done in the WHERE clause. The operators which can be used to JOIN the tables are - =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. However, it can be observed that the = is the most common operator used to JOIN tables.

Types of SQL JOINS:


There are several types of JOINS which can be performed in a table, they are following:
  • INNER JOIN: It returns rows when there is a match in both tables.
  • LEFT JOIN: It returns all rows from the left table, even if there are no matches in the right table.
  • RIGHT JOIN: It returns all rows from the right table, even if there are no matches in the left table.
  • FULL JOIN: It returns rows when there is a match in one of the tables.
  • SELF JOIN: It 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.
  • CARTESIAN JOIN: It returns the Cartesian product of the sets of records from the two or more joined tables

Inner Join:

Also reffered to as EQUIJOIN, INNER JOIN is one of the most important and frequently used JOIN in SQL.
INNER JOIN creates new table containg results by combining values of different tables based upon the join-predicate. The query compares each row of different tables to find all pairs of rows which satisfy the join-predicate.

Syntax:

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


Left Join:

In SQL, a LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. In other words it can be said that, if ON clause matches no records in the right table, result table will still be returned, but with a NULL value in case of no match in the right table. Therefore, the LEFT JOIN returns all values from the left table along with the matched values from the right table and NULL value if there is no matching join-predicate.

Syntax:

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


Right Join:

Similar to a LEFT JOIN, we have a RIGHT JOIN too. A RIGHT JOIN returns all the rows from the right table, even if there are no matches in the left table or we can say that if ON clause matches no records in the left table, a NULL value will be returned in case of no match in the left table.

Syntax:

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


Full Join:

The FULL JOIN in SQL combines the result obtained from both left and right outer joins. And therefore, the new joined table will contains all the result from both right and left tables and NULL value for missing matches on either side.

Syntax:

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


However, in MySQL, a FULL JOIN is not supported. Therefore, we can use UNION ALL clause for the same.

Syntax:

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


Self Join:

We can use SELF JOIN in SQL to join a table to itself. It treats them as two tables, and one table is temporarily renamed in the SQL statement.

Syntax:

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;


Cartesian Join:

The CARTESIAN JOIN is also known as the CROSS JOIN. It is used to return cartesian product of sets of records from two or more joined tables. We can also say that it is similar to the inner join where the condition is absent or always evaluates to true.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]


You can use the following image as a poster to quickly remember the topic of JOINs in SQL which we have discussed above: