Querying Multiple Tables By Using JOINS

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.

[info]Remember that result set of FROM clause is virtual table in SQL Server environments.[/info]

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:

[table id=16/]

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 :

[codesyntax lang=”tsql”]

Select T1.Name , T1.Age , T1.Height , T2.Name , T2.Age , T2.Height
from Table1 as T1 , Table2 as T2;

[/codesyntax]

Syntax ANSI SQL-92 :

[codesyntax lang=”tsql”]

Select T1.Name , T1.Age , T1.Height , T2.Name , T2.Age , T2.Height
from Table1 as T1 cross join Table2 as T2;

[/codesyntax]

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:

[codesyntax lang=”tsql”]

Select T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
from Table1 as T1 , Table2 as T2
where T1.Age = T2.Age

[/codesyntax]

Syntax ANSI SQL-92:

[codesyntax lang=”tsql”]

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

[/codesyntax]

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

Syntax :

[codesyntax lang=”tsql”]

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

[/codesyntax]

Result set :

Syntax :

[codesyntax lang=”tsql”]

Select T1.Name ,T2.Name from Table1 as T1 inner join Table2 as T2
on T1.Age = T2.Age and T1.Height = T2.Height

[/codesyntax]

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 :

[codesyntax lang=”tsql”]

Select ... from Table1
Inner join Table2 on ...
Inner join Table3 on ...
Inner join Table4 on ...
...

[/codesyntax]

Left Outer Join

Syntax :

[codesyntax lang=”tsql”]

Select T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
from Table1 as T1 left outer join Table2 
on T1.Age = T2.Age

[/codesyntax]

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 :

[codesyntax lang=”tsql”]

Select T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
from Table1 as T1 right outer join Table2 
on T1.Age = T2.Age

[/codesyntax]

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 :

[codesyntax lang=”tsql”]

Select T1.Name , T1.Height , T1.Age , T2.Name , T2.Height
from Table1 as T1 full outer join Table2 
on T1.Age = T2.Age

[/codesyntax]

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 :

[codesyntax lang=”tsql”]

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.NameT2E.Name

[/codesyntax]

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 .

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

Leave a Reply

avatar
  Subscribe  
Notify of

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Leave a Reply

avatar
  Subscribe  
Notify of

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan