SQL Is there an opposite but equivalent UNION function?

Question

Is there an opposite but equivalent UNION function for mysql?

I know this can be done with a longer request, but I'm curious if there is an equivalent function.

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 out only from table1 and ONLY if idname, phone, mobile, home from table1 does not match table2

For example: Table 1 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 request will display

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

Will NOT be displayed

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

Answer 1

In standard SQL, you can use the ANSI SQL EXCEPT statement, which is the exact equivalent of UNION

 SELECT * FROM Table2 EXCEPT SELECT * FROM Table1 

There is also an INTERSECT that shows strings that have both sources.

Unfortunately, none of them are supported in current versions of MySQL, so you need to use one of these three other more detailed ways to achieve anti-join.

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

Answer 2

If you use Oracle, you will use the MINUS clause.

 statement 1 MINUS statement 2 

or

 statement 1 WHERE NOT EXISTS (statement 2)