Opposite of UNION

Databases
1

The 1st query calls row(1), row(4) and the 2nd query calls row(2) and row(4).

The 1st query UNION the 2nd query calls row(1), row(2) and row(4).

Then, How can I call common row only?

The 1st query OPPOSITE of UNION 2nd query calls row(4) only.

Is there any operator or function which is OPPOSITE of UNION?

2

Normally you would do that with an INTERSECT (which is the opposite of union), but as far as I know MySQL doesn’t support INTERSECT.

So what you could do is something like:


SELECT
  field
FROM
   my_table
WHERE
   field IN (<second select query>)
3

you can do several unions so lon as you ensure they return the same number of values


select
col1
, col2
, NULL
from table 1 AS t1
UNION ALL
select
col2
, NULL
, NULL
from table AS t2
UNION ALL
select 
NULL
, col3
, col4
from table AS t3

Thats not a full query but is meant to show how using NULL can let you have a query with differing numbers of columns in each union. ALso meant to show UNION ALL, which you can google for.