What are SQL Joins and how tables are related to each other

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

Relational databases rely on relationships between their entities (tables and views).

However, in order to be able to call associated records from two or more tables / views above, we must somehow declare with which field they are connected to each other.

This is exactly what using SQL Joins does.

There are five types of Joins: Left Join, Right Join, Inner Join, Full Join, Cross join.

Let's explain the most basic ones one by one with examples.

To begin with, we will make 2 tables with data so that the difference between each type is easier to understand:

create table pelatis (
  id integer,
  id_xwra integer,
  onoma_pelati varchar(50));
create table xwres (
  id integer,
  onoma_xwras varchar(50));

insert into pelatis values
(1,1,'Stratos'),
(2,1,'Maria'),
(3,4,'Georgia'),
(4,10,'Nikos'),
(5,null,'Iasonas');

insert into xwres values
(1,'Ellada'),
(2,'Ispania'),
(3,'Italia'),
(4,'Agglia'),
(5,'Germania');

select * from pelatis;

select * from xwres;

Left Join

In Left Join we bring all the records of the first table even if there are no records matching the second table in the related field.

In the example we declare that we do a left join of the second table "xwres" in the field "id_xwres" of the first, with the field "id" of the second.

The result is that those records that have not been defined with a common id also appear, but with the "id" appearing null (i.e. empty).

select * from pelatis
left join xwres on pelatis.id_xwra = xwres.id;

Left Join with only the outer records

We apply this case when we want to find which records that exist in the first table are not associated in the second.

There are two ways for the same result the first is a normal one left join, using this time where in the field they are associated with when they are null in the second table.

select * from pelatis
left join xwres on pelatis.id_xwra = xwres.id
where xwres.id is null;

The same number of records will be returned if instead of join and is null we use correlated subquery exists (select).

select * from pelatis
where not exists (select xwres.id from xwres where xwres.id = pelatis.id_xwra);

Right Join

The right join works in reverse of the left join, i.e. we bring all the records of the second table even if there are no records matching the first table in the field they are associated with.

This time all records from the 'xwres' table will be displayed and those not associated with the 'pelatis' table will be displayed but as null in the field.

select * from pelatis
right join xwres on pelatis.id_xwra = xwres.id;

Inner Join

In the Inner Join we bring only the records that are associated in the field with both tables

By declaring inner before the join (although if we don't put anything it is the default) this time we will see the records that are associated, that is, only those customers who have a declared country.

select * from pelatis
inner join xwres on pelatis.id_xwra = xwres.id;

Full Join

In Full Join we bring all the records of the first table and all the records of the second table either related or not to each other in the field.

By executing the full join we will see that they will appear and customers who do not have a country and the customers they have and as many countries as do not have a customer.

select * from pelatis
full join xwres on pelatis.id_xwra = xwres.id;

Cross Join

With Cross Join we create a Cartesian product, that is, we combine the records of both tables with every possible combination.

select onoma_pelati,onoma|_xwras from pelatis
cross join xwres;

This was the analysis of Joins, their use needs a lot of care because many times we use Inner Join and we lose records that are not related to each other even though we wanted them. This leads to a wrong result.

Best practice is to use Left or Right Joins where Inner is not required.