The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
Consider the following two tables, (a) EMP table is as follows:
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 7369 | SMITH | CLERK | 7902 | 1993-06-13 | 800.00 | 0.00 | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1998-08-15 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1996-03-26 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1995-10-31 | 2975.00 | 20 | |
| 7698 | BLAKE | MANAGER | 7839 | 1992-06-11 | 2850.00 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1993-05-14 | 2450.00 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1996-03-05 | 3000.00 | 20 | |
| 7839 | KING | PRESIDENT | 1990-06-09 | 5000.00 | 0.00 | 10 | |
| 7844 | TURNER | SALESMAN | 7698 | 1995-06-04 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1999-06-04 | 1100.00 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 2000-06-23 | 950.00 | 30 | |
| 7934 | MILLER | CLERK | 7782 | 2000-01-21 | 1300.00 | 10 | |
| 7902 | FORD | ANALYST | 7566 | 1997-12-05 | 3000.00 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1998-12-05 | 1250.00 | 1400.00 | 30 |
(b) Another table is DEPT as follows:
| deptno | dname | location |
| 40 | Operations | Boston |
| 30 | Sales | Chicago |
| 20 | Research | Dallas |
| 10 | Accounting | New York |
Now, let us join these two tables in our SELECT statement as follows:
SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL, EMP.DEPTNO FROM EMP, DEPT WHERE emp.deptno = dept.deptno;
This would produce the following result:
SQL Join Types: There are different types of joins available in SQL:
- INNER JOIN: returns rows when there is a match in both tables.
- LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
- FULL JOIN: returns rows when there is a match in one of the tables.
- SELF JOIN: 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: returns the Cartesian product of the sets of records from the two or more joined tables.
INNER JOIN: The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN. The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax: The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2… FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
Example: Consider the following two tables, (a) EMP table is as follows:
(b) Another table is DEPT as follows:
Now, let us join these two tables using INNER JOIN as follows:
SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL, EMP.DEPTNO FROM EMP INNER JOIN DEPT ON emp.deptno = dept.deptno;
This would produce the following result:
LEFT JOIN: The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table. This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
Syntax: The basic syntax of LEFT JOIN is as follows:
SELECT table1.column1, table2.column2… FROM table1 LEFT JOIN table2 ON table1.common_filed = table2.common_field;
Example: Consider the following two tables,
(a) EMP table is as follows:
(b) Another table is DEPT as follows:
Now, let us join these two tables using LEFT JOIN as follows:
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
RIGHT JOIN: The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that 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.
Syntax: The basic syntax of RIGHT JOIN is as follows:
SELECT table1.column1, table2.column2… FROM table1 RIGHT JOIN table2 ON table1.common_filed = table2.common_field;
Example: Consider the following two tables, EMP and DEPT table:
Now, let us join these two tables using RIGHT JOIN as follows:
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
FULL JOIN: The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Syntax: The basic syntax of FULL JOIN is as follows:
SELECT table1.column1, table2.column2… FROM table1 FULL JOIN table2 ON table1.common_filed = table2.common_field;
Example: Consider the following two tables, EMP and DEPT table:
Now, let us join these two tables using FULL JOIN as follows:
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP FULL JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
SELF JOIN: The SQL SELF JOIN 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.
Syntax: The basic syntax of SELF JOIN is as follows:
SELECT a.column_name, b.column_name… FROM table1 a, table1 b WHERE a.common_filed = b.common_field;
Example: Consider the following two tables, EMP and DEPT table:
SELECT a.EMPNO, a.ENAME, a.SAL, a.HIREDATE FROM EMP a, DEPT b WHERE a.DEPTNO = b.DEPTNO;
CARTESIAN JOIN: The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
Syntax: The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2… FROM table1, table2 [, table3 ]
Example: Consider the following two tables, EMP and DEPT table:
Now, let us join these two tables using INNER JOIN as follows:
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP , DEPT ;
Total 56 records will come.