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
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.
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.
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.
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.
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With