Postgres offers set operators that make it easy to query and filter the results of searches from your database. Set operators are used to join the results of two or more SELECT statements. These operators are UNION, UNION ALL, INTERSECT, and EXCEPT-each can be used to construct queries across multiple tables and filter for the specific data that you need.
To return the combined results of two SELECT statements, we use the UNION set operator. This operator removes all the duplicates from the queried results-only listing one row for each duplicated result. To examine this behavior, the UNION ALL set operator can be used, as it retains duplicates in the final result. The INTERSECT set operator only lists records that are shared by both SELECT queries and conversely, the EXCEPT set operator removes the results from the second SELECT query. Thus, the INTERSECT and EXCEPT set operators are used to producing unduplicated results.
Database Fundamentals #23: Filtering Data
If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement.
The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they don't understand well enough what the filters and operators they're using will do. They end up returning too much data because they didn't us the WHERE clause or misapplied it. They also filter too much data out.
Database Fundamentals #22: Using the Join Operator, CROSS JOIN
While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY:
SELECT c.CompanyName,
p.LastName,
SUM(ft.TransactionAmount) AS 'TransactionTotals'
FROM Management.Company AS c
JOIN Finance.FinancialTransaction AS ft
ON c.CompanyID = ft.CompanyID
JOIN Personnel.Person AS p
ON p.PersonID = ft.PersonID
GROUP BY c.CompanyName, p.LastName;You might also like: Overview of SQL Joins in SQL Server
This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: