Page 131 - SQL

https://riptutorial.com/ 113

Cross Join SQL Pictorial Presentation (reference) :

Below are examples from this answer.

For instance there are two tables as below :

A B

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner Join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the

select * from a INNER JOIN b on a.a = b.b;

Left outer join

A left outer join will give all rows in A, plus any common rows in B:

select * from a LEFT OUTER JOIN b on a.a = b.b;

Right outer join

Similarly, a right outer join will give all rows in B, plus any common rows in A:

select * from a RIGHT OUTER JOIN b on a.a = b.b;

Full outer join

A full outer join will give you the union of A and B, i.e., all the rows in A and all the rows in B. If s
select * from a FULL OUTER JOIN b on a.a = b.b;

JOIN Terminology: Inner, Outer, Semi, Anti...

Let's say we have two tables (A and B) and some of their rows match (relative to the given JOIN

We can use various join types to include or exclude matching or non-matching rows from either si

The examples below use the following test data:

CREATE TABLE A (

Inner Join

Combines left and right rows that match.

SELECT * FROM A JOIN B ON X = Y;

Left Outer Join

Sometimes abbreviated to "left join". Combines left and right rows that match, and includes non-

SELECT * FROM A LEFT JOIN B ON X = Y;

Right Outer Join

Sometimes abbreviated to "right join". Combines left and right rows that match, and includes non-

SELECT * FROM A RIGHT JOIN B ON X = Y;

Full Outer Join

Sometimes abbreviated to "full join". Union of left and right outer join.

SELECT * FROM A FULL JOIN B ON X = Y;

Left Semi Join

Includes left rows that match right rows.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);

Right Semi Join

Includes right rows that match left rows.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);

As you can see, there is no dedicated IN syntax for left vs. right semi join - we achieve the effect s

Left Anti Semi Join

Includes left rows that do not match right rows.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);

WARNING: Be careful if you happen to be using NOT IN on a NULL-able column! More details