Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - Is there an opposite but equivalent UNION function?

Tags:

sql

mysql

Is there an opposite but equivalent UNION function for mysql?

I know it's possible to do this with a longer query but I'm curious on whether or not there's a function equivalent.

I have 2 select statements for 2 different tables

select state, state_name, id, idname, phone, mobile, home from table1
select state, state_name, id, idname, phone, mobile, home from table2

And I need a query that pulls only from table1 and ONLY if idname, phone, mobile, home from table1 doesn't match with table2

For example: Table1 has

AK | Alaska | 1 | row6   | 453  | 567 | 123

but Table 2 has:

AK | Alaska | 1 | row6   | 453  | 567 | 123
AK | Alaska | 1 | row6   | NULL | 567 | 123
AK | Alaska | 1 | tttttt | 453  | 567 | 123

The query will display

AK | Alaska | 1 | row6   | NULL | 567 | 123
AK | Alaska | 1 | tttttt | 453  | 567 | 123

will NOT display

AK | Alaska | 1 | row6   | 453  | 567 | 123
like image 506
Tonya S Avatar asked Sep 27 '12 18:09

Tonya S


People also ask

What is the opposite of UNION in SQL?

The UNION operator returns all rows. The INTERSECT operator returns all rows that are in both result sets. The EXCEPT operator returns the rows that are only in the first result set but not in the second.

What is alternative to UNION in SQL?

There are several alternatives to the union SQL operator: Use UNION ALL. Execute each SQL separately and merge and sort the result sets within your program! Sometimes, an external sort may be faster. Join the tables.

Is UNION and INTERSECT same in SQL?

The UNION operation combines the results of two subqueries into a single result that comprises the rows that are returned by both queries. The INTERSECT operation combines the results of two queries into a single result that comprises all the rows common to both queries.

What is the opposite of except in SQL?

EXCEPT returns distinct rows from the left input query that aren't output by the right input query. INTERSECT returns distinct rows that are output by both the left and right input queries operator.


1 Answers

In standard SQL you can use the ANSI SQL EXCEPT operator which is an exact analogue of UNION

SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1

There is also an INTERSECT set operator that would show rows that both sources have in common.

Unfortunately neither of these are supported in current versions of MySQL so you need to use one of these other 3 more verbose ways of achieving an anti semi join.

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL

like image 111
Martin Smith Avatar answered Sep 22 '22 18:09

Martin Smith