By continuing to browse, you consent to our use of cookies. You can read our Cookie Policy here.
INTERSECT -- looking for common values A hint to MySQL users: MySQL (5. 5) does not support INTERSECT operation. But as it is not an elementary operation, there are workarounds. EXCEPT [ edit] The EXCEPT operation evaluates to those values, which are in the first intermediate result but not in the second. -- All firstname except for 'Victor', because there is a lastname with this value. EXCEPT -- are there values in the result of the first SELECT but not of second? A hint to MySQL users: MySQL (5. 5) does not support the EXCEPT operation. But as it is not an elementary operation, there are workarounds. A hint to Oracle users: Oracle uses the keyword MINUS instead of EXCEPT. -- Clean up the example database DELETE FROM person WHERE id > 10; Order By [ edit] We can combine set operations with all other elements of SELECT command, in particular with ORDER BY and GROUP BY. But this may lead to some uncertainties. Therefore, we would like to explain some of the details below. ORDER BY firstname; To which part of the command belongs the ORDER BY?
This means mainly that they must have the same number of columns. Also, their data type should correlate, but this is not mandatory. If they do not, the DBMS will cast them to a common data type - if possible. UNION [ edit] The UNION operation pushes the result of several SELECT commands together. The result of the UNION contains those values, which are in the first or the second intermediate result. -- Please consider that this is only one command (only ONE semicolon at the very end) SELECT firstname -- first SELECT command FROM person UNION -- push both intermediate results together to one result SELECT lastname -- second SELECT command FROM person; The UNION of two intermediate results This is a single SQL command. It consists of two SELECTs and one UNION operation. The SELECTs are evaluated first. Afterward, their results are pushed together to one single result. In our example, the result contains all lastnames and firstnames in a single column (our example may be of limited help in praxis, it's only a demonstration for the UNION).
Why? Extend the previous solution to show as much resulting rows as hits to the criteria. -- Extend 'UNION' to 'UNION ALL'. The default is 'UNION DISTINCT' WHERE contact_type = 'email';
DISTINCT / ALL If we examine the result carefully, we will notice that it consists only of 17 values. The table person contains ten rows so that we probably expect twenty values in the result. If we perform the 'SELECT firstname... ' and 'SELECT lastname... ' as separate commands without the UNION, we receive for both commands ten values. The explanation for the three missing values is the UNION command. By default, UNION removes duplicates. Therefore some of the intermediate values are skipped. If we want to obtain the duplicate values we have to modify the UNION operation. Its behavior can be changed with one of the two keywords DISTINCT or ALL. DISTINCT is the default, and it removes duplicate values as we have seen before. ALL will retain all values, including duplicates. -- remove (that's the default) or keep duplicates SELECT... UNION [ DISTINCT | ALL] [ -- it is possible to 'UNION' more than 2 intermediate results SELECT... ]; A hint for Oracle users: The use of the keyword DISTINCT, which is the default, is not accepted by Oracle.
SELECT 'Min', min ( weight) WHERE place_of_birth = 'San Francisco' SELECT 'Max', max ( weight) SELECT 'Avg', avg ( weight) WHERE place_of_birth = 'San Francisco'; Extend the previous solution to order the result: the minimum value first, followed by the average and then the highest value. -- 'ugly' solution SELECT '1 Min' AS note, min ( weight) SELECT '3 Max' AS note, max ( weight) SELECT '2 Avg' AS note, avg ( weight) ORDER BY note; -- 'clean' solution SELECT 1 AS note, 'Min', min ( weight) SELECT 3 AS note, 'Max', max ( weight) SELECT 2 AS note, 'Avg', avg ( weight) Create a list of lastnames for persons with a weight greater than 70 kg together with all e-mail values (one value per row). There is no concordance between lastnames and e-mails. (This example is not very helpfull for praxis, but instructive. ) WHERE weight > 70 SELECT contact_value FROM contact WHERE contact_type = 'email'; In the previous example the lastname 'de Winter' is shown only once. But there are more than one persons of the family with a weight greater than 70 kg.
Omit it. General hint In most cases, the UNION combines SELECT commands on different tables or different columns of the same table. SELECT commands on the same column of a single table usually use the WHERE clause in combination with boolean logic. -- A very unusual example. People apply such queries on the same table only in combination with very complex WHERE conditions. -- This example would normally be expressed with a phrasing similar to: WHERE lastname IN ('de Winter', 'Goldstein'); SELECT * WHERE lastname = 'de Winter' UNION ALL WHERE lastname = 'Goldstein'; INTERSECT [ edit] The INTERSECT operation evaluates to those values, which are in both intermediate results, in the first as well as in the second. -- As in our example database, there is no example for the INTERSECT we insert a new person. -- This person has the same last name 'Victor' as the first name of another person. INSERT INTO person VALUES ( 21, 'Paul', 'Victor', DATE '1966-04-02', 'Washington', '078-05-1121', 66); COMMIT; -- All firstnames which are used as lastname.