Cross Join
Cross Join produces Cartesian product of rows from tables involved in the join. It returns all possible paired combination of each row from the first table and each row in the second table.
SYNTAX:
SELECT T1.Column1, T2.Column2 …
FROM TABLE1 AS T1
CROSS JOIN TABLE2 AS T2
Cross Join
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Table: Location
LocationID LocationCode LocationName
1 L001 San Francisco
2 L002 New York
3 L003 Chicago
4 L004 Los Angeles
Example
Query :
SELECT E.EmployeeID, E.LastName, E.FirstName, L.*
FROM Employee AS E
CROSS JOIN Location AS L
Output :
EmployeeID FirstName LastName LocationID LocationCode LocationName
1 George Cruz 1 L001 San Francisco
2 John Mathew 1 L001 San Francisco
3 Ethan William 1 L001 San Francisco
4 Jacob Logan 1 L001 San Francisco
5 Nathan Mateo 1 L001 San Francisco
6 Alice Charles 1 L001 San Francisco
7 John Robert 1 L001 San Francisco
8 Sophia James 1 L001 San Francisco
9 Kevin Chan 1 L001 San Francisco
10 Susan Mortiz 1 L001 San Francisco
11 Martha Miller 1 L001 San Francisco
1 George Cruz 2 L002 New York
2 John Mathew 2 L002 New York
3 Ethan William 2 L002 New York
4 Jacob Logan 2 L002 New York
5 Nathan Mateo 2 L002 New York
6 Alice Charles 2 L002 New York
7 John Robert 2 L002 New York
8 Sophia James 2 L002 New York
9 Kevin Chan 2 L002 New York
10 Susan Mortiz 2 L002 New York
11 Martha Miller 2 L002 New York
1 George Cruz 3 L003 Chicago
2 John Mathew 3 L003 Chicago
3 Ethan William 3 L003 Chicago
4 Jacob Logan 3 L003 Chicago
5 Nathan Mateo 3 L003 Chicago
6 Alice Charles 3 L003 Chicago
7 John Robert 3 L003 Chicago
8 Sophia James 3 L003 Chicago
9 Kevin Chan 3 L003 Chicago
10 Susan Mortiz 3 L003 Chicago
11 Martha Miller 3 L003 Chicago
1 George Cruz 4 L004 Los Angeles
2 John Mathew 4 L004 Los Angeles
3 Ethan William 4 L004 Los Angeles
4 Jacob Logan 4 L004 Los Angeles
5 Nathan Mateo 4 L004 Los Angeles
6 Alice Charles 4 L004 Los Angeles
7 John Robert 4 L004 Los Angeles
8 Sophia James 4 L004 Los Angeles
9 Kevin Chan 4 L004 Los Angeles
10 Susan Mortiz 4 L004 Los Angeles
11 Martha Miller 4 L004 Los Angeles
Description :
The above query returns Cartesian product of records from both Employee table and Location Table. Since the Employee table has 11 records and Location table has 4 records, the output of the query will have 11 * 4 = 44 rows.