SQL JOIN

• ChatGPT

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

ID	Name
1	Alice
2	Bob
3	Charlie

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

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 OUTER JOIN

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 OUTER JOIN

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

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

• JOIN SCHEMA

• Using Cross Joins

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

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

• JOIN Three Tables

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