Tuesday 10 October 2017
SQL JOINS
Type Of SQL Join:
- (INNER) JOIN: Returns records that have matching values in both tables.
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table.
The General syntax is
SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 =
common_name2 where condition;
Example of SQL Join's
SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 =
common_name2 where condition;
- (INNER) JOIN: The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
SELECT table1.column1, table2.column2...FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
Consider the following two tables.
Table 1 − CUSTOMERS Table is as follows.
+---+---------+-----+--------+----------+|ID | NAME | AGE | ADDRESS| SALARY |+---+---------+-----+--------+----------+| 1 | Manoj | 27 | Delhi | 2000.00 || 2 | Kumar | 25 | Bhopal | 1500.00 || 3 | Rajesh | 23 | Kota | 2000.00 |+---+---------+-----+--------+----------+Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+| OID | DATE | CUSTOMER_ID | AMOUNT |+-----+---------------------+-------------+--------+| 102 | 2017-10-08 00:00:00 | 1 | 3000 || 100 | 2017-10-08 00:00:00 | 1 | 1500 || 101 | 2017-11-20 00:00:00 | 2 | 1560 |+-----+---------------------+-------------+--------+These two tables using the INNER JOIN as follows
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Output:
+----+----------+--------+---------------------+| ID | NAME | AMOUNT | DATE |+----+----------+--------+---------------------+| 1 | Manoj | 3000 | 2017-10-08 00:00:00 || 1 | Manoj | 1500 | 2017-10-08 00:00:00 || 2 | Kumar | 1560 | 2017-11-20 00:00:00 |+----+----------+--------+---------------------+ - LEFT (OUTER) JOIN: The LEFT JOIN keyword returns all records from the left table (table1),
and the matched records from the right table (table2). The result is NULL from
the right side, if there is no match.
SELECT table1.column1, table2.column2...FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
Consider the following two tables.
Table 1 − CUSTOMERS Table is as follows.
+---+---------+-----+--------+----------+|ID | NAME | AGE | ADDRESS| SALARY |+---+---------+-----+--------+----------+| 1 | Manoj | 27 | Delhi | 2000.00 || 2 | Kumar | 25 | Bhopal | 1500.00 || 3 | Rajesh | 23 | Kota | 2000.00 |+---+---------+-----+--------+----------+Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+| OID | DATE | CUSTOMER_ID | AMOUNT |+-----+---------------------+-------------+--------+| 102 | 2017-10-08 00:00:00 | 1 | 3000 || 100 | 2017-10-08 00:00:00 | 1 | 1500 || 101 | 2017-11-20 00:00:00 | 2 | 1560 |+-----+---------------------+-------------+--------+These two tables using the LEFT JOIN as follows
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Output:
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
+----+----------+--------+---------------------+| ID | NAME | AMOUNT | DATE |+----+----------+--------+---------------------+| 1 | Manoj | 3000 | 2017-10-08 00:00:00 || 1 | Manoj | 1500 | 2017-10-08 00:00:00 || 2 | Kumar | 1560 | 2017-11-20 00:00:00 || 3 | Rajesh | Null | Null |+----+----------+--------+---------------------+ - RIGHT (OUTER) JOIN: The RIGHT JOIN keyword returns all records from the right table (table2),
and the matched records from the left table (table1). The result is NULL from
the left side, when there is no match.
SELECT table1.column1, table2.column2...FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
Consider the following two tables.
Table 1 − CUSTOMERS Table is as follows.
+---+---------+-----+--------+----------+|ID | NAME | AGE | ADDRESS| SALARY |+---+---------+-----+--------+----------+| 1 | Manoj | 27 | Delhi | 2000.00 || 2 | Kumar | 25 | Bhopal | 1500.00 || 3 | Rajesh | 23 | Kota | 2000.00 |+---+---------+-----+--------+----------+Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+| OID | DATE | CUSTOMER_ID | AMOUNT |+-----+---------------------+-------------+--------+| 102 | 2017-10-08 00:00:00 | 1 | 3000 || 100 | 2017-10-08 00:00:00 | 1 | 1500 || 101 | 2017-11-20 00:00:00 | 2 | 1560 |+-----+---------------------+-------------+--------+These two tables using the RIGHT JOIN as follows
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Output:
Note: The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).
+----+----------+--------+---------------------+| ID | NAME | AMOUNT | DATE |+----+----------+--------+---------------------+| 1 | Manoj | 3000 | 2017-10-08 00:00:00 || 1 | Manoj | 1500 | 2017-10-08 00:00:00 || 2 | Kumar | 1560 | 2017-11-20 00:00:00 |+----+----------+--------+---------------------+ - FULL (OUTER) JOIN: The FULL OUTER JOIN keyword return all records when there is a match in
either left (table1) or right (table2) table records.
SELECT table1.column1, table2.column2...FROM table1 FULL OUTER JOIN table2 ON table1.common_field = table2.common_field;
Note: FULL OUTER JOIN can potentially return very large result-sets!
Consider the following two tables.
Table 1 − CUSTOMERS Table is as follows.
+----+----------+-----+-----------+----------+| ID | NAME | AGE | ADDRESS | SALARY |+----+----------+-----+-----------+----------+| 1 | Ramesh | 32 | Ahmedabad | 2000.00 || 2 | Khilan | 25 | Delhi | 1500.00 || 3 | kaushik | 23 | Kota | 2000.00 |+----+----------+-----+-----------+----------+Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+|OID | DATE | CUSTOMER_ID | AMOUNT |+-----+---------------------+-------------+--------+| 102 | 2017-10-08 00:00:00 | 3 | 3000 || 100 | 2017-10-08 00:00:00 | 3 | 1500 || 101 | 2017-11-20 00:00:00 | 2 | 1560 |+-----+---------------------+-------------+--------+These two tables using the FULL OUTER JOIN as follows
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS FULL OUTER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Output:
Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders” or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
+------+----------+--------+---------------------+| ID | NAME | AMOUNT | DATE |+------+----------+--------+---------------------+| 1 | Ramesh | NULL | NULL || 2 | Khilan | 1560 | 2017-11-20 00:00:00 || 3 | kaushik | 3000 | 2017-10-08 00:00:00 || 3 | kaushik | 1500 | 2017-10-08 00:00:00 || 3 | kaushik | 3000 | 2017-10-08 00:00:00 || 3 | kaushik | 1500 | 2017-10-08 00:00:00 || 2 | Khilan | 1560 | 2017-11-20 00:00:00 |+------+----------+--------+---------------------+
Subscribe to:
Posts
(
Atom
)
