I had to write a few SQL queries today so I thought I would toss up a few notes as a sort of SQL FAQ. I'll try to keep these notes updated.
- Inner joins fail on null matches
- When you are joining tables, if you want to keep null values from your foreign key (i.e. the thing you're trying to match your second table on), you need to use a left outer join.
- Inner joins on one-to-many
- When joining one-to-many, an inner join will give you a line for each match to the second table; e.g. if I have one row in table A and it matches on two rows in table B, you will get two rows- one row for each match
- What table should I start with when writing a query?
- When writing a query, the best place to start is to figure out what the output should look like. This is important because it helps you determine the granularity of your report. Once you've done this, now, when you're faced with a myriad of tables, you start with the table that matches the granularity of the report you wish to write.
Order Matters
Queries are written in the following order:
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
However, they are processed in this order:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
This isn't just an academic distinction, it can be helpful in troubleshooting your reports when you get either an error or results you don't expect.