SQL Join is used to fetch data from two or more tables, which is
joined to appear as a single set of data.
SQL Join is used for combining column from two or more tables by using values
common to both tables. Join Keyword is used in SQL queries for joining two or
more tables. Minimum required condition for joining table is (n-1) where n, is a number
of tables. A table can also join to itself known as, Self-Join.
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 query
will produce below 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.
Below are the different types of Join 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.
Example:
Orders
|
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
Customers
|
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
Table : Join Command Example
Inner Join:
1. SELECT Orders.OrderID, Customers.CustomerName,
Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
2. SELECT Orders.OrderID,
Customers.CustomerName, Shippers.ShipperName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID =
Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
Left Join:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID
ORDER BY Customers.CustomerName;
Right Join:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Full Outer
Join:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Self-Join:
SELECT A.CustomerName AS CustomerName1,
B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;