Skip to content

Notes on science, software and the craft of support

Martin EvansNotes and resources

Databases

SQL joins explained

Joins combine rows from two or more tables into one result, with inner, left, right and full outer joins covering the common cases.

SQL joins are a fundamental part of any database query language. They are used to combine data from multiple tables into one result set. A join is a clause in an SQL statement that indicates how two or more tables are related.

Joins can retrieve data from multiple tables in a single query, which allows data to be drawn from several sources with one statement. They can perform tasks such as combining data from multiple tables, creating complex queries that span multiple tables and combining tables into one temporary table.

The four main joins

There are four main types of join: inner join, left join, right join and full outer join.

  • An inner join is the most commonly used join. It returns rows only when there is a match between two tables. An inner join combines the rows from both tables that meet the join condition. For example, to query the names of all the students in a school, an inner join can combine the student and school tables.
  • A left join returns all the rows from the left table, even if there are no matches in the right table. It is used to return all rows from the left table and the matching rows from the right table. For example, to query the names of all the students and the names of their teachers, a left join can combine the student and teacher tables.
  • A right join works in the same way as a left join, except that it returns all the rows from the right table, even if there are no matches in the left table. For example, to query the names of all the teachers and the names of their students, a right join can combine the teacher and student tables.
  • A full outer join returns all the rows from both tables, regardless of whether there are matches. For example, to query the names of all the students and the names of their teachers, a full outer join can combine the student and teacher tables.

SQL joins are an essential part of any database query language. They provide a flexible way to combine data from multiple tables into one result set. By understanding the different types of join and their uses, developers can create complex queries that span multiple tables and combine them into a single temporary table.