Querying Multiple Tables By Using JOINS

The estimated reading time for this post is 3 minutes

In SQL Server environments, when user wants to retrieve data from multiple tables needs to make join between those tables based on date relationship between tables. In SELECT statement order of writing tables name is important because the table after FROM clause will be considered as LEFT table and the table after JOIN will be considered as RIGHT table.

Remember that result set of FROM clause is virtual table in SQL Server environments.

The first operation in all kind of JOINS is Cartesian product which is the result set of joining every row of one input table to all rows of another input table, without predicates SQL Server query processor will output all possible combinations of rows.

There are three kinds of JOIN as below:

Join TypeDescription
Cross JoinCombines all rows in both tables ( Cartesian product )
Inner Join or just JoinStarts with Cartesian product , applies filter to match rows between tables based on predicates
(Left Outer Join or Left Join ) and ( Right Outer Join or Right Join ) and ( Full Outer Join or Outer Join )Starts with Cartesian product , all rows from designated table preserved , matching rows from other table retrieved , additional NULLs inserted as placeholder

Now these examples will show you features, benefits of any kinds of JOINS and differences between those.

Consider we have two tables as below:

The use of table aliases improves the readability of query , without affecting the performance . It is strongly recommended that you use table aliases in  your multi tables queries .

Cross join

Syntax ANSI SQL-89 :

SELECT T1.Name , T1.Age , T1.Height , T2.Name , T2.Age , T2.Height
FROM Table1 AS T1 , Table2 AS T2;

Syntax ANSI SQL-92 :

SELECT T1.Name , T1.Age , T1.Height , T2.Name , T2.Age , T2.Height
FROM Table1 AS T1 cross join Table2 AS T2;

Result set : Cartesian product (10×10=100 rows)

Inner join

Inner joins are the most common types to solve many business problems ,especially in highly normalized database environments .

Syntax ANSI SQL-89:

SELECT T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
FROM Table1 AS T1 , Table2 AS T2
WHERE T1.Age = T2.Age

Syntax ANSI SQL-92:

SELECT T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
FROM Table1 AS T1 INNER join Table2 AS T2
ON T1.Age = T2.Age

Result set : At first it makes Cartesian product and then shows each row that can match to predicates in ON clause

Syntax :

SELECT T1.Name , T1.Age , T1.Height , T2.Name , T2.Age 
FROM Table1 AS T1 INNER join Table2 AS T2
ON T1.Height = T2.Height

Result set :

Syntax :

SELECT T1.Name ,T2.Name FROM Table1 AS T1 INNER join Table2 AS T2
ON T1.Age = T2.Age and T1.Height = T2.Height

Result set :

It is easy to query many tables in T-SQL by JOIN , consider you have some tables in database which they have data relation ship , you can querying all tables as below :

SELECT ... FROM Table1
INNER join Table2 ON ...
INNER join Table3 ON ...
INNER join Table4 ON ...
...

Left Outer Join

Syntax :

SELECT T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
FROM Table1 AS T1 LEFT outer join Table2 
ON T1.Age = T2.Age

Result set : At first it makes Cartesian product then shows all rows of left table and every rows from right table that can match to predicates in ON clause

Right Outer Join

Syntax :

SELECT T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
FROM Table1 AS T1 RIGHT outer join Table2 
ON T1.Age = T2.Age

Result set : At first it makes Cartesian product then shows all rows of right table and every rows from left table that can match to predicates in ON clause

Full Outer Join

Syntax :

SELECT T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
FROM Table1 AS T1 FULL outer join Table2 
ON T1.Age = T2.Age

Result set : At first it makes Cartesian product then shows all rows from left and right tables that can match to predicates in On clause

Self Join

Syntax :

SELECT T2.Name , T2.Age , T2E.Name , T2.Age
FROM Table2 AS T2 INNER join Table2 AS T2E
ON T2.Height = T2E.Height WHERE T2.Name<>T2E.Name

Result set : At first it makes Cartesian product then shows each rows that can match to predicates in ON and WHERE clause

Note : Table aliases are convenient tool and optional except in case of self join queries .

This post is a summary of all kinds of joins which you can use in your multi tables queries but for increasing your knowledge study more sources and practice more examples .

 

 

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website