SQL Joins

SQL Aliases :

  1. SQL Joins clause is used to combine records from two or more tables in a database.
  2. SQL Join is used to combine fields from fields from two tables by using values common to each other

Type of Join :

Different types of joins -

Type of Join Description
Inner join All the rows will be returned that are having match in both tables.
Left join All the rows will be returned from the left table even if there are no matches in the right table.
Right join All the rows will be returned from the right table even if there are no matches in the left table.
Full join All the rows will be returned that are having match in one of the tables.
Self join It is used to join itself by renaming the table with another name and pretending that there are two tables.
Cartesian join All the rows will be returned as the result of Cartesian product of the sets of records from the two or more joined tables.

Demo Database :

Consider the following demo table Called - “CUSTOMER”

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Arnav    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

and following table “ORDER”

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2024-11-02 00:00:00 |           3 |   2000 |
| 100 | 2024-11-02 00:00:00 |           3 |   3500 |
| 101 | 2024-11-24 00:00:00 |           2 |   4500 |
| 103 | 2025-03-20 00:00:00 |           4 |   5000 |
+-----+---------------------+-------------+--------+

SQL Joins Clause :

Consider below query on demo database -

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

will result into 

+----+----------+-----+-----------+
| ID | ENAME    | AGE | AMOUNT    |
+----+----------+-----+-----------+
|  3 | Ram      |  21 | 2000      |
|  3 | Ram      |  21 | 3500      |
|  2 | Raj      |  23 | 4500      |
|  4 | Saurabh  |  25 | 5000      |
+----+----------+-----+-----------+

In the above query we have joined two tables. Each record of CUSTOMER table will be checked with each record from the ORDER table like below -

Records Calculation Count
Total Number of Records in CUSTOMER 7
Total Number of Records in ORDER 4
Total Number of Comparisons 28
Total Records Selected 4