SQL file to download
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. SQL Joins are table operators used to combine columns from one or more tables. The expression(predicate) that define the columns which are used to join the tables is called Join Predicate. The result of a join is a set (relational database implementation of a set). ANSI standard recognizes five types of joins: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join and Cross Join.
Joins are typically used to retrieve data from the normalized tables usually with an equality predicate between primary and foreign key columns. To understand examples of SQL joins I will be working with these 3 table: tblEmployee, tblDepartment, tblTerritory. You can download these files from the attached document.
1. INNER JOIN
SQL Server INNER JOINS return all rows from multiple tables where the join condition is met. Only rows with values satisfying the join conditions are displayed as result set. You can use joins to create a temporary tables or new tables.
Select * from tblEmployee E
INNER JOINtblDepartment D
ON E.DepID = D.DepID
2. OUTER JOIN
2.1 LEFT OUTER JOIN
The Left Outer Join returns all the rows from the table specified on the LEFT and the matching rows from the table specified on the RIGHT side of the LEFT OUTER JOIN keyword. NULL values are displayed in the columns of the right-side table where matching rows are not found with the left side table.
Select * from tblEmployee E
LEFT OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
Select * from tblEmployee E
LEFT OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
WHERE D.DepID IS NULL
2.2 RIGHT OUTER JOIN
The Right Outer Join returns all the rows from the table specified on the RIGHT and the matching rows from the table specified on the LEFT side of the RIGHT OUTER JOIN keyword. NULL values are displayed in the columns of the left side table where matching rows are not found with the right-side table.
Select * from tblEmployee E
RIGHT OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
Select * from tblEmployee E
RIGHT OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
WHERE E.DepID IS NULL
2.3 Full outer Join
A Full Outer Join is a combination of left and right outer join. This join returns all the matching and non-matching values from both the tables. However, in case of non-matching values a NULL value is displayed
Select * from tblEmployee E
FULL OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
Select * from tblEmployee E
FULL OUTER JOIN tblDepartment D
ON E.DepID = D.DepID
WHERE E.DepID IS NULL
OR
D.DepID IS NULL
3. CROSS JOIN
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.
If WHERE clause is used with CROSS JOIN, it works like an INNER JOIN
select * from Car
CROSS JOIN CarColours
Understanding Three-table Joins
The way it works is relatively simple to understand. First join two tables, get some result and then combine the result with the third table.
In the next example we will first join 2 tables tblEmployee and tblDepartment. We are going to get back some data and after then we will combine these data with the third table tblTerritory.
A. Inner join 2 tables
A. Inner join 3 tables
It doesn’t matter in which order the tables have been joined the final result set will be the same. You just have to be careful and used the correct foreign keys from tables.
Short overview of t-sql joins:
