Technology LearningData in a well-built database is spread across tables on purpose. Patients in one table, visits in another. A join is how you bring related rows back together in a single result. The confusing part is never the joining itself. It is what a join does with rows that have no match on the other side, and that is exactly what the four join types decide.
The setup
Picture two tables. patients has one row per person. visits has one row per appointment, and each visit names the patient it belongs to. You want a result that shows each visit next to the patient it was for. You join them on the patient identifier they share.
The question every join type answers differently is this: what about a patient with no visits, or a visit with no matching patient?
INNER JOIN: only the matches
An inner join keeps a row only when the match exists on both sides. Patients with no visits vanish from the result. Visits with no known patient vanish too. You are left with clean pairs and nothing else.
Reach for it when you only care about rows that connect, which is most of the time. If you want "every visit and who it was for", and every visit really does have a patient, an inner join is right.
LEFT JOIN: keep everything on the left
A left join keeps every row from the first table you named, matched or not. Patients with no visits still appear, with the visit columns left empty. This is the join for "show me all patients, and their visits if they have any".
The empty side is the point. A left join is how you find the gaps: patients with no visits show up with blanks where the visit should be, so a left join plus a test for those blanks is the standard way to find "everyone who has not yet been seen".
RIGHT JOIN: keep everything on the right
A right join is the mirror image. It keeps every row from the second table instead. In practice people rarely write right joins, because you can always swap the two tables and use a left join, which reads more naturally. Knowing it exists is enough.
FULL JOIN: keep everything on both sides
A full join keeps every row from both tables, matched or not, filling blanks on whichever side is missing. Use it when both sides have rows the other might not, and you want to see all of it at once, including what fails to line up.
The most common mistake
A join with no ON condition, or the wrong one, does not fail. It quietly pairs every row with every row, and you get thousands of nonsense rows back. If a join returns far more rows than you expected, check the condition before anything else.
Where to go next
Joins are the single most important SQL skill after SELECT, and the difference between an inner and a left join is the difference between "who was seen" and "who was not". SQL JOINs teaches all four with worked queries, and once they feel natural Advanced Joins covers self-joins and the trickier cases.