INDBS Noter

Tags:conceptdatabaseSQLDML Status:🟩

SQL Joins

Summary

Simple queries only retrieve data from one table. If we want to retrieve data from multiple table, we will have to join tables together using the JOIN statement. (More advanced image below)

Details

Joining 2 tables can be done by specifying another table separated by a comma. The result corresponds to the cartesian product, and will not necessary have any relationship between the 2 tables.

SELECT * FROM students, universities;

Inner Joins

To join a table with relationship to another table, INNER JOIN can be used. Inner join returns only the records with matching data in both tables being joined. When using this statement, its important to specify the primary key and foreign key that’s related in the tables using the ON clause.

SELECT name FROM students 
INNER JOIN joined_courses ON (students.id = joined_course.studentid)

It’s not necessary to write INNER. Writing JOIN by itself is equivalent to writing INNER JOIN.

Outer Join

Outer Join returns all records from one or both tables being joined even if there is no matching data in the other table.

An inner join returns only the rows where there is a match in both tables, while an outer join returns all rows from one or both tables, including unmatched rows, with NULL for missing data.

There are 3 different types of outer join:

  • Left outer join
  • Right outer join
  • Full outer join

Left outer join

Returns all rows from the left table. If there’s no match in the right table, columns from the right table will be NULL.

SELECT s.id, s.name, c.id, c.name
FROM students AS s
LEFT OUTER JOIN courses AS c ON s.student_id = c.student_id;

This gets all students and the courses they’re enrolled in, even if some students aren’t enrolled in any courses.

Right outer join

Returns all rows from the right table. If there’s no match in the left table, columns from the left table will be NULL.

SELECT c.id, c.name, s.id, s.name
FROM courses AS c
RIGHT OUTER JOIN students AS s ON c.student_id = s.student_id;

This gets all courses and the students enrolled in them, even if some courses don’t have any students enrolled.

Full outer join

Returns all rows from both tables. If there’s no match, the result will have NULL values for columns from the table without a match.

SELECT s.student_id, s.student_name, c.course_id, c.course_name
FROM students AS s
FULL OUTER JOIN courses AS c ON s.student_id = c.student_id;

To get all students and all courses, including students with no courses and courses with no students.

Natural Join

A Natural Join combines rows from two tables based on columns with the same names and compatible data types. It automatically joins the tables using these common columns. The result includes each row from the first table that has matching rows in the second table, with the common columns appearing only once in the output.

The join operation identifies columns with the same names in both tables and performs the join based on these columns. Only one instance of the common columns is included in the result set.

SELECT * FROM Employees
NATURAL JOIN Departments;

It is almost like a inner join. The key differences is:

  • NATURAL JOIN: Implicitly joins on all columns with the same names.
  • INNER JOIN: Explicitly specifies the join condition using ON.
  • NATURAL JOIN: Common columns are only listed once in the result.
  • INNER JOIN: Includes all specified columns from both tables.

Cross Join

A Cross Join produces a Cartesian product of two tables, pairing each row of the first table with every row of the second table. This results in a large number of rows if both tables have many entries. The total number of rows in the result is the product of the number of rows in each table.

Every row from the first table is combined with every row from the second table. This join does not require any conditions and generates all possible row combinations.

SELECT * FROM Products
CROSS JOIN Colors;

Self Join

A Self Join is used to join a table with itself to compare rows within the same table. It is helpful for querying hierarchical data or finding relationships between rows in the same table. The table is aliased to differentiate between the two instances.

The table is given two aliases to act as if it were two separate tables. This allows for comparing rows within the same table based on specified conditions.

SELECT e1.Name AS Employee, e2.Name AS Manager 
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

This gets all employees and their managers, even if some employees don’t have a manager.

Examples

Result from an left outer join

Result from an right outer join

Result from an outer full join

All joins visualization

Graph View

Backlinks