Joins in postgreSQL

Joins in postgreSQL:

PostgreSQL joins combine rows from two or more tables based on a related column between them.

Table 1 Teams

Table 2 Player

1. INNER JOIN (Default):

• Returns records only when the join condition is met in both tables.

select team.team_id, player.player_name, team.joined_date from teams
inner join player
on
teams.player_id = player.player_id;

2. LEFT JOIN (or LEFT OUTER JOIN):

• Returns all rows from the left table.
• If no match exists on the right, it outputs NULL.
• Perfect for finding users who haven't placed an order.

select * from
teams left join player
on
teams.player_id = player.player_id;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

• The exact mirror of LEFT JOIN. It guarantees all rows from the right table are returned.

select * from
teams right join player
on teams.player_id = player.player_id;

4. FULL OUTER JOIN:

• Combines the behavior of both LEFT and RIGHT joins.
• It displays everything, matching rows where possible and inserting NULL where a row has no counterpart.

select * from teams
full outer join
player on team.player_id = player.player_id;

5. CROSS JOIN:(TBD)

• Creates a grid of all possible combinations by multiplying the left table rows by the right table rows.
• Do not use an ON clause here.