SQL - Is there an opposite but equivalent UNION function?

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

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


If you're using Oracle, you'll use the MINUS clause.

statement 1
MINUS
statement 2

or

statement 1
WHERE NOT EXISTS (statement 2)

Need Your Help

Laravel: how to render only one section of a template?

laravel laravel-4

I'm trying to use pjax on my site, which means that for a full page request I render the whole template (this is normal behaviour), but on pjax requests I would like to render just one section. My

In Python, why is list(None) an error but [None] is not?

python list

Passing None to Python's list constructor is a TypeError: