SQL-Lesson #4

PRE-STEPS

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);
 
CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');
 
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');

INNER JOIN

EXAMPLE

SELECT 
    m.member_id,
    m.name as member_name,
    c.committee_id,
    c.name as committees_name
FROM
    members m
INNER JOIN committees c 
    ON c.name = m.name;

LEFT JOIN

EXAMPLE

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
LEFT JOIN committees c USING(name);

RIGHT JOIN

EXAMPLE

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
RIGHT JOIN committees c USING(name);

CROSS JOIN

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
CROSS JOIN committees c;

Made with Slides.com