SELECT Query with OUTER JOIN
OUTER JOINS are of 3 types: LEFT OUTER, RIGHT OUTER and FULL OUTER
Returns all the records from table 1 and table 2 and fill in NULLS for missing and matches on either side
SELECT table1.column1, table2.column2...
FROM table1
FULL 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 | Ranith | 32 | Ahmedabad | 2000.00 |
| 2 | Krupa | 25 | Delhi | 1500.00 |
| 3 | kaulik | 23 | Kota | 2000.00 |
| 4 | Chaitan | 25 | Mumbai | 6500.00 |
| 5 | Munny | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|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, using FULL JOIN let us join these two tables as follows.
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ranith | NULL | NULL |
| 2 | Krupa | 1560 | 2009-11-20 00:00:00 |
| 3 | kaulik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaulik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitan | 2060 | 2008-05-20 00:00:00 |
| 7 | Munny | NULL | NULL |
| 3 | kaulik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaulik | 1500 | 2009-10-08 00:00:00 |
| 2 | Krupa | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitan | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+--------------------+
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials