SQL Joins and Syntax

Vocabulary flashcards covering the concepts, syntax rules, diagrammatic representations, and behaviors of INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL.

INNER JOIN

A SQL join that returns only rows matching on a specified column that exists in more than one table, returning all specified columns from both tables.

Inner Join (Venn Diagram)

A join operation where only the intersecting middle region shared between the left table and right table is selected.

Left Table

The first table specified in a SQL join query, placed immediately after the FROM clause.

Right Table

The second table specified in a SQL join query, placed immediately after the join keyword (such as INNER JOIN or LEFT JOIN).

ON keyword

A SQL keyword placed after the right table name that works with the $=$ operator to specify the column used to join two tables.

Table and Column Qualification (Dot Notation)

The syntax of placing a period between a table name and a column name (such as employees.device_id) to explicitly indicate which table's column to reference when that column exists in multiple tables.

SELECT * Behavior on Shared Columns

The behavior in SQL join queries where any column present in both tables is returned twice in the result set when selecting all columns.

Outer Joins

A category of SQL joins that expand output by returning all rows from either one table or both tables rather than only returning matching rows.

LEFT JOIN

A join type that returns all records from the first (left) table and only matching rows from the second (right) table based on a specified column.

Left Join (Venn Diagram)

A join operation where the entire left table and the overlapping intersection with the right table are selected.

RIGHT JOIN

A join type that returns all records from the second (right) table and only matching rows from the first (left) table based on a specified column.

Right Join (Venn Diagram)

A join operation where the entire right table and the overlapping intersection with the left table are selected.

Table Order Reversal (LEFT JOIN and RIGHT JOIN)

The rule stating that a LEFT JOIN and a RIGHT JOIN yield identical results if the order of the tables before and after the join keyword is switched.

FULL OUTER JOIN

A join type that returns all records from both tables, functioning as a complete merge of two tables.

Full Outer Join (Venn Diagram)

A join operation where both the left table and the right table are completely selected.

Table Order Invariance

The property shared by INNER JOIN and FULL OUTER JOIN where reversing the order of the two joined tables does not alter the query results.