SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. JoinKeyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is(n-1) where n, is number of tables. A table can also join to itself known as, Self Join.
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 −
Table 1 − CUSTOMERS Table
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Table 2 − ORDERS Table
+-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 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 | +-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as shown below.
SQL> SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result.
+----+----------+-----+--------+ | ID | NAME | AGE | AMOUNT | +----+----------+-----+--------+ | 3 | kaushik | 23 | 3000 | | 3 | kaushik | 23 | 1500 | | 2 | Khilan | 25 | 1560 | | 4 | Chaitali | 25 | 2060 | +----+----------+-----+--------+
Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.
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.
Types of Join
The following are the types of JOIN that we can use in SQL.
- Inner
- Outer
- Left
- Right
Cross JOIN or Cartesian Product
This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.
Cross JOIN Syntax is,
SELECT column-name-list from table-name1 CROSS JOIN table-name2;
Example of Cross JOIN
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 4 | alex |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
Cross JOIN query will be,
SELECT * from class, cross JOIN class_info;
The result table will look like,
| ID | NAME | ID | Address |
|---|---|---|---|
| 1 | abhi | 1 | DELHI |
| 2 | adam | 1 | DELHI |
| 4 | alex | 1 | DELHI |
| 1 | abhi | 2 | MUMBAI |
| 2 | adam | 2 | MUMBAI |
| 4 | alex | 2 | MUMBAI |
| 1 | abhi | 3 | CHENNAI |
| 2 | adam | 3 | CHENNAI |
| 4 | alex | 3 | CHENNAI |
INNER Join or EQUI Join
This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query.
Inner Join Syntax is,
SELECT column-name-list from table-name1 INNER JOIN table-name2 WHERE table-name1.column-name = table-name2.column-name;
Example of Inner JOIN
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 3 | alex |
| 4 | anu |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
Inner JOIN query will be,
SELECT * from class, class_info where class.id = class_info.id;
The result table will look like,
| ID | NAME | ID | Address |
|---|---|---|---|
| 1 | abhi | 1 | DELHI |
| 2 | adam | 2 | MUMBAI |
| 3 | alex | 3 | CHENNAI |
Natural JOIN
Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.
Natural Join Syntax is,
SELECT * from table-name1 NATURAL JOIN table-name2;
Example of Natural JOIN
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 3 | alex |
| 4 | anu |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
Natural join query will be,
SELECT * from class NATURAL JOIN class_info;
The result table will look like,
| ID | NAME | Address |
|---|---|---|
| 1 | abhi | DELHI |
| 2 | adam | MUMBAI |
| 3 | alex | CHENNAI |
In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.
Outer JOIN
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,
- Left Outer Join
- Right Outer Join
- Full Outer Join
Left Outer Join
The left outer join returns a result table with the matched data of two tables then remaining rows of the lefttable and null for the right table’s column.
Left Outer Join syntax is,
SELECT column-name-list from table-name1 LEFT OUTER JOIN table-name2 on table-name1.column-name = table-name2.column-name;
Left outer Join Syntax for Oracle is,
select column-name-list from table-name1, table-name2 on table-name1.column-name = table-name2.column-name(+);
Example of Left Outer Join
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 3 | alex |
| 4 | anu |
| 5 | ashish |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
| 7 | NOIDA |
| 8 | PANIPAT |
Left Outer Join query will be,
SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id=class_info.id);
The result table will look like,
| ID | NAME | ID | Address |
|---|---|---|---|
| 1 | abhi | 1 | DELHI |
| 2 | adam | 2 | MUMBAI |
| 3 | alex | 3 | CHENNAI |
| 4 | anu | null | null |
| 5 | ashish | null | null |
Right Outer Join
The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.
Right Outer Join Syntax is,
select column-name-list from table-name1 RIGHT OUTER JOIN table-name2 on table-name1.column-name = table-name2.column-name;
Right outer Join Syntax for Oracle is,
select column-name-list from table-name1, table-name2 on table-name1.column-name(+) = table-name2.column-name;
Example of Right Outer Join
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 3 | alex |
| 4 | anu |
| 5 | ashish |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
| 7 | NOIDA |
| 8 | PANIPAT |
Right Outer Join query will be,
SELECT * FROM class RIGHT OUTER JOIN class_info on (class.id=class_info.id);
The result table will look like,
| ID | NAME | ID | Address |
|---|---|---|---|
| 1 | abhi | 1 | DELHI |
| 2 | adam | 2 | MUMBAI |
| 3 | alex | 3 | CHENNAI |
| null | null | 7 | NOIDA |
| null | null | 8 | PANIPAT |
Full Outer Join
The full outer join returns a result table with the matched data of two table then remaining rows of both lefttable and then the right table.
Full Outer Join Syntax is,
select column-name-list from table-name1 FULL OUTER JOIN table-name2 on table-name1.column-name = table-name2.column-name;
Example of Full outer join is,
The class table,
| ID | NAME |
|---|---|
| 1 | abhi |
| 2 | adam |
| 3 | alex |
| 4 | anu |
| 5 | ashish |
The class_info table,
| ID | Address |
|---|---|
| 1 | DELHI |
| 2 | MUMBAI |
| 3 | CHENNAI |
| 7 | NOIDA |
| 8 | PANIPAT |
Full Outer Join query will be like,
SELECT * FROM class FULL OUTER JOIN class_info on (class.id=class_info.id);
The result table will look like,
| ID | NAME | ID | Address |
|---|---|---|---|
| 1 | abhi | 1 | DELHI |
| 2 | adam | 2 | MUMBAI |
| 3 | alex | 3 | CHENNAI |
| 4 | anu | null | null |
| 5 | ashish | null | null |
| null | null | 7 | NOIDA |
| null | null | 8 | PANIPAT |