Them that are joined together thou shall not put asunder!! not even through an analysis of windows function.
I actually don't know why this phrase keeps popping up in my head whenever i think of this topic, well let me indulge you;
i will give you a very simple illustration of joins in a marriage set-up this will remain etched in your brains forever.
To understand JOINS Think of two families and their relation, in two tables
Table 1 The Bride Table;
| bride_id | bride_name |
|---|---|
| 1 | Mary |
| 2 | Anna |
| 3 | Grace |
Table 2 The Grooms Table;
| groom_id | bride_id | groom_name |
|---|---|---|
| 10 | 1 | John |
| 11 | 2 | Peter |
We will use a join to connect this two tables using the bride_id as the Connecting Factor.
Thus
A join is used to connect two tables in a scenario where the two tables have a related column.
Let me share another simple example to make understanding even more easier.
say you have;
Table 1 with the following columns and rows;
|student_id| name|
|0001| Mary|
|0002| John|
Table 2
| name| age|
| Mary | 39 |
| john| 42 |
These two tables have |name| column as a common column.
What this means essentially is that you can connect Table 1 to Table 2 using a join.
This is how you will join in SQL
----show student_id and age from Table 1 and connect name table on matching column,
Types of Joins
Inner Join
These allow or they return only the rows that match or have matching values in both tables.
in short only the matching stuff Married couples only
see this easy example for clarity
Example 1
Table 1 The Bride Table;
| bride_id | bride_name |
| -------- | ---------- |
| 1 | Mary |
| 2 | Anna |
| 3 | Grace |
Table 2The Grooms Table;
| groom_id | bride_id | groom_name |
| -------- | -------- | ---------- |
| 10 | 1 | John |
| 11 | 2 | Peter |
Using an inner Join write the following query on*SQL*
select b. bride_name, g.groom_name
from Bride b
inner join Grooms g on b.bride_id= g bride_id;
NOTEGrace will dissappear as she is not married
Example 2
Customer Table.
| customer_id | name |
| ----------- | ----- |
| 1 | Mary |
| 2 | John |
| 3 | Sarah |
Transaction Table
| customer_id | amount |
| ----------- | ------ |
| 1 | 500 |
| 2 | 700 |
Run this query
select c.name, t.amount
from customer c
inner join transaction t on c.customer_id= t.transaction_id
left join
When joining using left join you are simply creating a query on SQL As follows
take everything from the left table match it with the right table if available if there is no match give me a null on the right tables.
see a simple example
Table 1
create table bride_right(
name varchar(20),
bride_id serial
);
insert into bride_right(name)values
('nancy_kariuki'),
('ann_kariuki'),
('margret_kariuki'),
('mary_kariuki');
create table groom(
groom_name varchar(20),
groom_id serial
);
drop table groom;
create table groom_left(
groom_name varchar(20),
groom_id serial
);
insert into groom_left(groom_name)values
('albert_kimani'),
('moses_kimani'),
('Joseph_kimani')
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
left join
groom_left g
on
b.bride_id = g.groom_id;
Right Join
This is the opposite of the left join.
keep all records from the right table match it with the left table if available if there is no match give me a null for the left tables.
Example
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
right join
groom_left g
on
b.bride_id = g.groom_id;
full outer Join
Shows all the rows from both tables.
it combines the right outer and left outer joins together.
Example
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
full outer join
groom_left g
on
b.bride_id = g.groom_id;
Cross Join
A CROSS JOIN creates every possible combination of rows between the two tables.
there is a slight difference when writting its querry we do not use on . Because it does not match rows based on a condition.
*Example
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
cross join
groom_left g
Self Join.
`This is Joining a table to itself.
For Example finding an employee who is a manager in an employee table,
the assumption is we have two tables in one table by using alias we separate the table into two.
use distinct key word
select distinct manager name(m)as manager,
from employee e
inner join employees m on e.manager_id= m.employee_id
use an inner join without conditions.
Example
select
b1.name as bride1,
b2.name as bride2
from
bride_right b1
join
bride_right b2
on b1.bride_id <> b2.bride_id;
Natural Join
automatically all tables using columns that have the same name
employee tables vs department table with a common table
select all from employees table natural join department; the department id becomes the unifying factor.
Example:
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
natural join
groom_left g
When choosing which join to use the most important question is which data must i not loose
Top comments (0)