MS-SQL Joins explained: Inner, Outer, Left, Right Join; Union

 

Joins can be used to get data in SQL from one or more tables in a single output. Joins are often combined with other queries in practice: For example, most often for the selection of data is not, as used here, a "select * from", but the respective columns are selected: "select column1,column2 from", or, of course, the joins can be combined or nested with "where" or other queries.

Test-Setup Microsoft SQL Server

For testing, I downloaded the free SQL Server 2019 Express version and the SQL Management Studio (SSMS). In addition, there is now even the option to install the SQL Server on Linux or in a Docker container.

:

I created 2 tables in the database: Persons and Place. With the Postcode column included in Persons, we can get the associated Place from the Postcode table.
I have intentionally not created certain Postcodes here, or also used Postcodes that do not appear in the Persons table, so that the effect of the joins can be better seen.

Table persons

id First name last name Postcode
1 Amelia Smith 90802
2 Grace Jones 91105
3 Jessica Williams 91977
4 Emma Taylor 90274
5 Evie Brown 92037
6 Isabelle Meyer 90402

Table place

id Place Postcode
1 Long Beach 90802
2 Pasadena 91105
3 La Jolla 92037
4 La Jolla 90308
5 Spring Valley 91977
6 Beverly Hills 90209

INNER JOIN

An inner join connects only columns where the specified check is true, others are omitted:

in SQL Management Studio: New Query:

select * from persons INNER JOIN place ON persons.Postcode = place.Postcode

and execute: Execute:

Result:

As a result we get the data of both tables where there is a match, i.e. the Postcode coincides in both tables:

id first name last name Postcode id Place Postcode
1 Amelia Smith 90802 1 Long Beach 90802
2 Grace Jones 91105 2 Pasadena 91105
5 Evie Brown 92037 3 La Jolla 92037
3 Jessica Williams 91977 5 Spring Valley 91977

LEFT JOIN

A left join uses the first table and, if possible, joins the data with the 2nd table. If there is no matching entry in the 2nd table, NULL is entered as value:

select * from persons LEFT JOIN place ON persons.Postcode = place.Postcode

Result

The result of a left join is the data of the first table and if possible matches from the 2nd table:

id first name last name Postcode id Place Postcode
1 Amelia Smith 90802 1 Long Beach 90802
2 Grace Jones 91105 2 Pasadena 91105
3 Jessica Williams 91977 5 Spring Valley 91977
4 Emma Taylor 90274 ZERO ZERO ZERO
5 Evie Brown 92037 3 La Jolla 92037
6 Isabelle Meyer 90402 ZERO ZERO NULL

RIGHT JOIN

A right join is similar to a left join but uses the 2nd table as a base and searches for entries in the 1st table. Also here the fields are filled with NULL for which there is no match:

select * from persons RIGHT JOIN place ON persons.Postcode = place.Postcode

Result

The result of a right join is the data of the second table and if possible hits from the first table:

id first name last name Postcode id Place Postcode
1 Amelia Smith 90802 1 Long Beach 90802
2 Grace Jones 91105 2 Pasadena 91105
5 Evie Brown 92037 3 La Jolla 92037
ZERO ZERO ZERO ZERO 4 La Jolla 90308
3 Jessica Williams 91977 5 Spring Valley 91977
ZERO ZERO ZERO ZERO 6 Beverly Hills 90209

FULL JOIN (FULL OUTER JOIN)

Last but not Isabellest the "Full-Join", often also called "Full Outer JOIN". Here all data from both columns are joined, if there is no match, this is again filled with "NULL

select * from persons FULL JOIN place ON persons.Postcode = place.Postcode

Result

The result of a full join is the data of both tables and if possible hits from the other table:

id first name last name Postcode id Place Postcode
1 Amelia Smith 90802 1 Long Beach 90802
2 Grace Jones 91105 2 Pasadena 91105
3 Jessica Williams 91977 5 Spring Valley 91977
4 Emma Taylor 90274 ZERO ZERO ZERO
5 Evie Brown 92037 3 La Jolla 92037
6 Isabelle Meyer 90402 ZERO ZERO NULL
ZERO NULL NULL ZERO 4 La Jolla 90308
ZERO ZERO ZERO ZERO 6 Beverly Hills 90209

UNION

With the help of a UNION 2 tables can be joined to one. For this I create another table: Persons2 and add another person there:

Table Persons2

id first name last name Postcode
1 Cordula Green 92037

Query

SELECT * FROM persons
UNION
SELECT * FROM persons2;

Result

The result of a UNION is the data of both tables, since the columns are identical, simply appended:

id first name last name Postcode
1 Cordula Green 92037
1 Amelia Smith 90802
2 Grace Jones 91105
3 Jessica Williams 91977
4 Emma Taylor 90274
5 Evie Brown 92037
6 Isabelle Meyer 90402
positive Bewertung({{pro_count}})
Rate Post:
{{percentage}} % positive
negative Bewertung({{con_count}})

THANK YOU for your review!

Questions / Comments


By continuing to browse the site, you agree to our use of cookies. More Details