SQL Joins

 

inner Vs left-semi Vs left Vs right (with examples)

• An INNER JOIN can return data from the columns from both tables, and can duplicate values of records on either side have more than one match. 
• A LEFT SEMI JOIN can only return columns from the left-hand table, and yields one of each record from the left-hand table where there is one or more matches in the right-hand table (regardless of the number of matches). It's equivalent to

-- create

CREATE TABLE e1 (

  id INTEGER

);

CREATE TABLE e2 (

  id INTEGER

);

-- insert

INSERT INTO e1 VALUES (1);

INSERT INTO e1 VALUES (1);

INSERT INTO e1 VALUES (1);

INSERT INTO e1 VALUES (1);

INSERT INTO e2 VALUES (1);

INSERT INTO e2 VALUES (NULL);

INSERT INTO e2 VALUES (1);

SELECT * FROM e1;

SELECT * FROM e2;

select * from e1 right join e2 

on e1.id = e2.id;