SQL JOINs are used to combine rows from two or more tables based on a related column between them. Here’s an explanation of different types of SQL JOINs with Venn diagrams and examples:
1. INNER JOIN
Definition: An INNER JOIN returns records that have matching values in both tables. It retrieves only the rows that satisfy the join condition.
Venn Diagram:
- A (left table) ∩ B (right table) = Intersection
Example:
Consider two tables:
Employees
Departments
| ID | EmployeeID | Department |
|---|
| 1 | 1 | HR |
| 2 | 2 | IT |
| 3 | 4 | Finance
|
SELECT Employees.Name, Departments.Department
FROM Employees
INNER JOIN Departments ON Employees.ID = Departments.EmployeeID;
Result:
| Name | Department |
|---|
| Alice | HR |
| Bob | IT |
2. LEFT OUTER JOIN
Definition: A LEFT OUTER JOIN returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the side of the right table.
Venn Diagram:
- A (left table) ∪ (A ∩ B) = Left table plus the intersection
SELECT Employees.Name, Departments.Department
FROM Employees
LEFT OUTER JOIN Departments ON Employees.ID = Departments.EmployeeID;
3. RIGHT OUTER JOIN
Definition: A RIGHT OUTER JOIN returns all records from the right table and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.
Venn Diagram:
- B (right table) ∪ (A ∩ B) = Right table plus the intersection
SELECT Employees.Name, Departments.Department
FROM Employees
RIGHT OUTER JOIN Departments ON Employees.ID = Departments.EmployeeID;
4. FULL OUTER JOIN
Definition: A FULL OUTER JOIN returns all records when there is a match in either left or right table. It combines the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN.
Venn Diagram:
- A (left table) ∪ B (right table) = Union of both tables
Example:
SQL Query:
SELECT Employees.Name, Departments.Department
FROM Employees
FULL OUTER JOIN Departments ON Employees.ID = Departments.EmployeeID;
Summary
- INNER JOIN: Retrieves only the rows with matching values in both tables (intersection).
- LEFT OUTER JOIN: Retrieves all rows from the left table and matching rows from the right table (left table plus intersection).
- RIGHT OUTER JOIN: Retrieves all rows from the right table and matching rows from the left table (right table plus intersection).
- FULL OUTER JOIN: Retrieves all rows from both tables, with
NULL where there are no matches (union of both tables).
- The SQL JOIN clause is used whenever we have to select data from 2 or more tables.
There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS.
The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on.
SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases),
selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.
The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).
References:
http://www.sql-tutorial.net/SQL-JOIN.asp
http://www.quackit.com/sql/tutorial/sql_outer_join.cfm
http://www.tizag.com/sqlTutorial/sqljoin.php
http://www.sqltutorial.org/sqljoin-innerjoin.aspx
Inner join shows matches only when they exist in both tables.this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
There is another way to visualize the SQL INNER JOIN by using the Venn diagrams
Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
There is another way to visualize the SQL INNER JOIN by using the Venn diagrams
Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
There is another way to visualize the SQL INNER JOIN by using the Venn diagrams
Full outer join it will return all records from left table and from right table.
There is another way to visualize the SQL INNER JOIN by using the Venn diagrams
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
The result set contains 170 rows (SalesPerson has 17 rows and SalesTerritory has 10; 17 multiplied by 10 equals 170).
http://msdn.microsoft.com/en-us/library/ms190690%28v=sql.105%29.aspx
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product
http://www.w3resource.com/sql/joins/cross-join.php
CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table
CREATE TABLE department
(
DepartmentID INT,
DepartmentName VARCHAR(20)
);
CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT
);
INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing');
INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);
Example of an explicit cross join:
SELECT *
FROM employee
CROSS JOIN department;
Example of an implicit cross join:
SELECT *
FROM employee, department;
Inner join
SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
The following example is equivalent to the previous one, but this time using implicit join notation:
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Left outer join
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
Right outer join
SELECT *
FROM employee
RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
Full outer join
SELECT *
FROM employee
FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
Self-join
A self-join is joining a table to itself
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
http://en.wikipedia.org/wiki/Join_%28SQL%29
method 1
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);
method 2
SELECT tablo1.OrderID, tablo1.CustomerName, Shippers.ShipperName
FROM (select * from Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
as tablo1
INNER JOIN Shippers ON tablo1.ShipperID = Shippers.ShipperID;
https://www.w3schools.com/sql/sql_join_inner.asp
