SQL - Using Joins

The SQL Joins clause is used to combine records from two or more tables in a database. In a relational database, the data stored are all related but scattered throughout the database as multiple tables. So, if there arises a need to retrieve cohesive data from multiple tables, a simple Join clause can be used. With a Join clause, fields from two tables can be combined by using values common to each.

A Join Clause works with respect to a join-predicate. This join-predicate is specified in a WHERE clause; so it is nothing but a condition that must be satisfied by database tables in order to combine them. For example, comparing the equapty (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc.

There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are psted as follows −

Inner Join − An Inner Join 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. This is a default join.

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, pke Inner Join. Outer join is further spanided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.

Syntax

Following is the basic syntax of a Join in SQL −

SELECT column_name(s)
FROM table_name1
JOIN table_name2;

Example

Consider the following two tables. Note that we are using existing tables in a local database; to create new tables, refer to CREATE TABLE and INSERT QUERY chapters in this tutorial.

Table 1 − CUSTOMERS Table. This table contains the data (personal details) about Customers of an organization.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  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. This table contains the order details made by the customers.

+-----+---------------------+-------------+--------+
|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, we are trying to perform the basic join operation on these two tables using the JOIN keyword in the SELECT statement as shown below.

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 tables and AMOUNT field of Orders table.

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitap |  25 |   2060 |
+----+----------+-----+--------+

Alternate Query

You can also join two tables without using the JOIN keyword in a SELECT statement as shown below −

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

Output

This would produce the following result.

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitap |  25 |   2060 |
+----+----------+-----+--------+

Let us now learn about the different types of joins available in SQL, in detail.

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.

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