SQL JOIN

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