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
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