The process of combining rows of two or more then two tables based on a common field between them are known as SQL joins.
Types of SQL joins :
INNER JOIN: It is returns rows only when at least one row matches from both tables.
LEFT JOIN: It is return rows from the left table,which matched rows from the right table.
RIGHT JOIN: It is return rows from the right table, which matched rows from the left table.
FULL JOIN: It is return rows when there is a match in ONE of the tables.
Here, I explain joins using below tables.
"Orders" Table
| OrderID |
CustomerID |
OrderDate |
EmployeeID |
| 103 |
2 |
1996-09-18 |
1 |
| 109 |
37 |
1996-09-19 |
2 |
| 1010 |
77 |
1996-09-20 |
3 |
"Customers" Table
| CustomerID |
CustomerName |
ContactName |
Country |
| 1 |
Chanda |
Chanda Rai |
Germany |
| 2 |
Babita |
Bob |
Mexico |
| 3 |
Manish |
Jon |
Mexico |
"Employees" Table
| EmployeeID |
LastName |
FirstName |
BirthDate |
| 1 |
Davolio |
Nancy |
12/8/1968 |
| 2 |
Fuller |
Andrew |
2/19/1952 |
| 3 |
Leverling |
Janet |
8/30/1963 |
INNER JOIN: The below SQL statement will return all customers with all orders:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
LEFT JOIN: The below SQL statement will return all customers, and orders they might have:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
RIGHT JOIN: The below SQL statement will return all customers, and orders they might have:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
FULL JOIN: The below SQL statement will return all employees, and orders they have placed:
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;
0 Comment(s)