Особенности JOIN в SQL и примеры использования

Немного о JOIN в SQL

Синтаксис оператора JOIN

SELECT
    t1.column_name,
    t2.column_name
FROM
    table_name1 as t1 
    [join_type] JOIN table_name2 as t2 ON join_condition;

FROM
    table_name1 as t1 
    [join_type] JOIN table_name2 as t2 ON join_condition;

Для чего нужен JOIN в SQL

Как работает JOIN

SELECT
    s.ROLL_NO,
    s.NAME,
    s.ADDRESS,
    s.PHONE,
    s.AGE,
    sc.COURSE_ID,
    c.NAME as COURSE_NAME,
FROM
    Student s
    JOIN StudentCourse sc ON s.roll_no = sc.roll_no
    JOIN Course c ON c.course_id = sc.course_id;

Кому пригодится оператор JOIN

Виды JOIN в SQL

INNER JOIN

SELECT
    a.id as address_id,
    u.full_name,
    u.enabled as user_enabled,
    u.last_login,
    u.id as user_id,
    a.street,
    a.city
FROM
    users as u
    INNER JOIN addresses as a ON u.id = a.user_id

OUTER JOIN

SELECT
    u.id as user_id,
    u.full_name,
    u.enabled as user_enabled,
    u.last_login,
    a.id as address_id,
    a.street,
    a.city
FROM
    users as u
    LEFT JOIN addresses as a ON user.id = addresses.user_id;

SELECT
    u.id as user_id,
    u.full_name,
    u.enabled as user_enabled,
    u.last_login,
    a.id as address_id,
    a.street,
    a.city
FROM
    addresses a
    RIGHT JOIN users u ON u.id = a.user_id;

CROSS JOIN

SELECT
    u.id as user_id,
    u.full_name,
    u.enabled as user_enabled,
    u.last_login,
    a.id as address_id,
    a.street,
    a.city
FROM
    users u,
    addresses a;

SELECT
    u.id as user_id,
    u.full_name,
    u.enabled as user_enabled,
    u.last_login,
    a.id as address_id,
    a.street,
    a.city
FROM
    users u
    CROSS JOIN addresses a;

SELF JOIN

Подведем итоги