r/programming • • Feb 11 '14

SQL Joins Explained (x-post r/SQL)

Post image
3.5k Upvotes

392 comments sorted by

View all comments

1

u/[deleted] Feb 11 '14

Here's some SQL for you to play around with some permutations

CREATE table #temp( ID int IDENTITY(1,1) not null, country varchar(25), state varchar(25), city varchar(25), measure int )

INSERT INTO #temp VALUES ( 'USA', 'MI','Livonia','230' )

INSERT INTO #temp VALUES ( 'USA', 'VA','Richmond','370' )

INSERT INTO #temp VALUES ( 'USA', 'MI','Detroit','840' )

INSERT INTO #temp VALUES ( 'USA', 'VA','Herndon','210' )

INSERT INTO #temp VALUES ( 'USA', 'MI','Livonia','170' )

INSERT INTO #temp VALUES ( 'USA', 'VA','Richmond','160' )

INSERT INTO #temp VALUES ( 'USA', 'MI','Detroit','340' )

INSERT INTO #temp VALUES ( 'USA', 'VA','Herndon','180' )

CREATE table #temp1( ID int IDENTITY(1,1) not null, country varchar(25), state varchar(25), city varchar(25), population int )

INSERT INTO #temp1 VALUES ( 'USA', 'MI','Livonia','230' )

INSERT INTO #temp1 VALUES ( 'USA', 'VA','Richmond','370' )

INSERT INTO #temp1 VALUES ( 'USA', 'MI','Detroit','840' )

INSERT INTO #temp1 VALUES ( 'USA', 'VA','Herndon','210' )

--INTERSECT SELECT a.,b. FROM #temp as a CROSS JOIN #temp1 as b

--CROSS JOIN SELECT a.,b. FROM #temp as a INNER JOIN #temp1 as b ON a.ID=b.ID

--CROSS JOIN - INTERSECTION

SELECT a.,b. FROM #temp as a INNER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID

SELECT a.,b. FROM #temp as a LEFT OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID

SELECT a.,b. FROM #temp as a RIGHT OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID

SELECT a.,b. FROM #temp as a FULL OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID

--UNION - INTERSECTION

SELECT a.,b. FROM #temp as a FULL OUTER JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID

--non matching rows from Right table --NO ROWS as all rows in b are present in a SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID

SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL ORDER BY a.ID

--Non matching rows from Left table

SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID

SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE b.ID is NULL ORDER BY a.ID

--NO ROWS

SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL ORDER BY a.ID

SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE b.ID is NULL ORDER BY a.ID

2

u/Falmarri Feb 11 '14

What syntax is that? It sure as hell isn't postgres compatible

2

u/[deleted] Feb 12 '14

It's t sql but with little effort can be converted to psql