Join Queries ​

Join queries are a type of multi-table query that allow us to combine data from multiple tables based on a related column. In simpler terms, we designate one table as the primary table and selectively "join" rows from other tables to create the result set.

Example of an Inner Join ​

Suppose we want to select all student information from the students table. We can do this with a simple SELECT statement:

-- Select all students:
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

However, if we also want to include the class name from the classes table, we need to join the two tables based on their related columns. For this, we can use an INNER JOIN:

-- Select all students and include class name:
SELECT s.id, s.name, s.class_id, c.name AS class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

In this query:

• We define the primary table with FROM students s.
• We specify the table to join with INNER JOIN classes c.
• We set the joining condition with ON s.class_id = c.id, which matches the class_id in students with the id in classes.

Types of Joins ​

• INNER JOIN: Returns only the rows that exist in both tables. In our example, it will return only students whose class_id matches an id in the classes table.

  

• OUTER JOIN: There are three types:

  • RIGHT OUTER JOIN: Returns all rows from the right table (in this case, classes), and fills in NULL for the columns from the left table (students) if there is no match:
  sql

  SELECT s.id, s.name, s.class_id, c.name AS class_name, s.gender, s.score
  FROM students s
  RIGHT OUTER JOIN classes c
  ON s.class_id = c.id;

  

  • LEFT OUTER JOIN: Returns all rows from the left table and fills in NULL for any non-matching rows from the right table:
  sql

  -- Add a student with a non-existent class_id
  INSERT INTO students (class_id, name, gender, score) VALUES (5, 'New Student', 'M', 88);
  
  SELECT s.id, s.name, s.class_id, c.name AS class_name, s.gender, s.score
  FROM students s
  LEFT OUTER JOIN classes c
  ON s.class_id = c.id;

  

  • FULL OUTER JOIN: Returns all rows from both tables, filling in NULL where there are no matches:
  sql

  SELECT s.id, s.name, s.class_id, c.name AS class_name, s.gender, s.score
  FROM students s
  FULL OUTER JOIN classes c
  ON s.class_id = c.id;

  

Choosing the Right Join ​

The choice of which join to use depends on the specific requirements of your query:

• INNER JOIN is the most common and returns only matching records.
• LEFT OUTER JOIN is useful when you want all records from the left table regardless of matches in the right table.
• RIGHT OUTER JOIN does the opposite, retaining all records from the right table.
• FULL OUTER JOIN is used when you need to retain all records from both tables, filling in NULL for non-matches.

Summary ​

Join queries require identifying a primary table and appending data from another table. The INNER JOIN is the most frequently used type, with the syntax SELECT ... FROM <table1> INNER JOIN <table2> ON <condition>. Joins can still use WHERE clauses and ORDER BY for sorting results.