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

Table place

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:

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:

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:

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:

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

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: