SQL Array and Set Operations: UNION, INTERSECT, and EXCEPT
Set operations combine result sets in ways that JOINs cannot. Here is how I use UNION, INTERSECT, and EXCEPT to solve real problems. Set operations combine the results of two queries into one. UNION combines rows from both queries, INTERSECT returns rows that appear in both, and EXCEPT returns rows that appear in the first but not the second. These are not joins, which combine columns from matching rows. Set operations combine rows from queries that may have nothing in common except their column structure. After years of using them, I find set operations indispensable for specific patterns that are awkward to express with joins. The Rules of Set Operations Both queries in a set operation must return the same number of columns, and corresponding columns must have compatible types. The column names come from the first query. Set operations remove duplicates by default. Adding the ALL keyword keeps duplicates, which is faster because the database does not need to sort or hash to deduplicate. . UNION: combine rows from both queries, duplicates removed SELECT product_id FROM cart_items UNION SELECT product_id FROM wishlist_items; . Returns every product that appears in any cart or…