Some useful reminders for the occasional SQL user.
SQL Query Anatomy
Most databases don't like it when the SQL clauses are out of order.
Null in SQL is special as it isn't equal (
=) to anything, not even itself, which seems wiered when coming from programming languages but makes perfect sense when considering that
NULL means "unknown value" wich definitely isn't equal to another "unknown value".
If you want null to behave more like in a programming language use the
is keyword, where
NULL is NULL evaluates to 1 opposed to
NULL = NULL which results in a 0.
Note: For all other values
is acts like
To get a default value at the time of a query there is the
coalesce() function which takes multiple aruments and returns the first non-null one. (SQLite)
SQLite has a page with more null quirks, comparing SQLite to other Database engines.
What all joins have in common is that they glue the tables together and output the entries where the
ON condition is true.
They differ in how they treat entries that don't make it into the output because they didn't find a "partner" using the
- only returns entries that found a partner.
- doesn't drop columns from the already present columns, leaving the joined columns
- additionally outputs all entries from the joined table that didn't find a partner, leaving the other columns
- like a
rightjoin, returning all of the entries in both tables, joining the ones that are join-able and filling in where joining isn't possible.
Note: To keep this example as clean as possible types have been omitted, the SQL is intended to be compatible with sqlite3. Use the command
sqlite3 :memory: to get an in memory database to play around with.
Assume we have two tables, called
right_t for illustration purposes, bot with a single column.
INSERT INTO left_t VALUES ('both'),('left'); INSERT INTO right_t VALUES ('both'),('right'); (lc); (rc);
Now we can query them an see the results:
SELECT lc,rc FROM left_t INNER JOIN right_t ON lc = rc ;
We now performed an inner join of
left_t on the single columns having the same value. The returned record is
both|both. Pretty intuitive.
Now we substitute the
INNER for a
LEFT and the result is:
All rows in
left_t table mad it through the join, the hole in the
right_t was filled with a (blank)
Note: If the
right_t table had more columns all of them would be returned as
NULL, independent of any
NOT NULL statements.
Similar results for a
Now all rows from the
right_t table made it through and the missing rows in the
left_t table was filled with a
And for the
both|both left| |right
All rows preserved, the ones that matched got joined that ones without matches had the resulting holes filled with