What are SQL Joins and how tables are related to each other
Certifications:
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.
