Union in mysql

Union in mysql

union is used to merge records

USE sample;
SELECT domicile
FROM employee_enh 

UNION

SELECT location
FROM department;

Here two select results are merged to become one result

Get the employee number for employees who either belong to department d1 or
entered their project before 1/1/2007, in ascending order of employee number:


USE sample;
SELECT emp_no
FROM employee
WHERE dept_no = 'd1'

UNION

SELECT emp_no
FROM works_on
WHERE enter_date < '01.01.2007'
ORDER BY 1;

INTERSECT and EXCEPT Set Operators

The two other set operators are INTERSECT, which specifies the intersection, and
EXCEPT, which defines the difference operator

USE sample;
SELECT emp_no
FROM employee
WHERE dept_no = 'd1'

INTERSECT

SELECT emp_no
FROM works_on
WHERE enter_date < '01.01.2008';
USE sample; 

--------------------------------------------------------------------------------------------------

SELECT emp_no
FROM employee
WHERE dept_no = 'd3' 

EXCEPT

SELECT emp_no
FROM works_on
WHERE enter_date > '01.01.2008';

INTERSECT operator has the highest priority, EXCEPT is evaluated next, and the UNION operator has the lowest priority. If you do not pay attention to these different priorities, you will get unexpected results when you use several set operators together.