USING
SELECT friend_id, e.name AS entree, d.name AS dessert
FROM entrees e
INNER JOIN desserts d USING (friend_id);
- This is the basic anatomy of a typical select statement below.
- The SELECT statement is used to select data from a database.
- The INNER JOIN keyword selects records that have matching values in both tables.
- The FROM command is used to specify which table to select or delete data from.
- The AS command is used to rename a column or table with an alias.
USING is helpful for simplifying your join when you are joining two tables on columns with the same name. In the above example, you have two tables which are lists of entrees and desserts and the ID of the friend who knows how to prepare them. If you have offers from multiple friends to come over for dinner, you want to know what possible combinations of entree and dessert each friend can make for you before you decide which offer to accept, so you run this query. USING makes it so you do not have to write out ON e.friend_id = d.friend_id, but what I find particularly helpful is that you no longer have to qualify which friend_id you are referring to. This prevents the ever-frustrating error ERROR: column reference “friend_id” is ambiguous when you forget to put e. or d. in front of friend_id
CASE
“SELECT CASE
WHEN c.country = ‘US’ THEN c.state
ELSE c.country END AS region FROM clients c;”
CASE operates similarly to if, else if, and else statements. It returns what comes after THEN for the first WHEN statement that is true. If none of the WHEN statements are true, it returns what is under the ELSE statement. In the example, you want to send a gift to each of your clients because of how much you appreciate them, but first, you want to approximate shipping costs so you need to find out where they all live. If they don’t live in the United States, you’re fine with just calculating the shipping costs based on the country, but otherwise, you want to know which specific state they live in. CASE allows you to check the country condition and return the shipping region based off that check.
String Pattern Matching (LIKE/ILIKE/~/~*)
SELECT b.title, b.author
FROM books b
WHERE b.title LIKE ‘%Pirate%’;
SELECT b.title, b.author
FROM books b
WHERE b.title ~ ‘Pirate’;
SELECT b.title, b.author
FROM books b
WHERE b.title ILIKE ‘%pirate%’;
SELECT b.title, b.author
FROM books b
WHERE b.title ~* ‘pirate’;
If you need to pattern match on a string, you are provided with quite a few options. The most performant option available to you is LIKE, which uses the built-in SQL matching including % for 0 or more characters. You also have the Postgres-exclusive ~ which has the power of regex behind it, if you need a more complicated match. Then, you have the case insensitive versions of both, ILIKE and ~* respectively. For the example, we see four versions of trying to find a book that mentions “Pirate” in the title, since you want to read an old school high seas adventure. Personally, I have always loved the ~* for quick queries where I just need to find something in a table quickly and the performance is not much of an issue, but I would recommend using LIKE or ILIKE for production code if possible.
UNION (or UNION ALL)
SELECT s.pricing_id, s.price
FROM snacks s
UNION
SELECT t.pricing_id, t.price
FROM tickets t
UNION
SELECT m.pricing_id, m.price
FROM memberships m
ORDER BY price;
UNION allows you to combine the results of multiple queries into one result set. In the example, we have a theater which has three types of products that they store in separate tables due to the different information required for each product type. They have snacks from the snack bar, tickets for the shows, and memberships that allow you to support the theater while also getting discounted prices on other purchases. The theater wants a list of all the prices of their products along with the ID used by their POS system for record-keeping purposes. UNION allows them to take the results from all three tables and bring the distinct ones together. This works as long as each SELECT returns the same number of columns and they columns have similar types. If you are willing to not worry about making each row distinct, you can use UNION ALL instead.
FILTER
SELECT m.id as member_id, COUNT() as member_count, COUNT() FILTER(WHERE m.expiration_date > current_date) as active_member_count
FROM members_m;
The COUNT() function returns the number of rows that matches a specified criterion.
FILTER gives you the ability to run an aggregate function over a subset of the overall result set. Let’s go back to the theater from the previous example for this one. Now, they want to know how many total members they have ever had and how many active members they currently have. To get overall total, you can just run COUNT, but you can run COUNT again with the additional FILTER to only get the members you have not hit their expiration date yet.
There are a number of manuals I use, here is one I use the most: