SQL Cheatsheet

SQL Query Anatomy

Most databases don't like it when the SQL clauses are out of order.

• Operator
  • SELECT … FROM … (SQLite)
  • INSERT INTO <table>(…) VALUES (…),… (SQLite)
  • UPDATE <table> SET … (SQLite)
  • DELETE FROM … (SQLite)
• JOIN
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• OFFSET

NULL Tricks

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.

Join Types

SELECT …
FROM bookmark
INNER JOIN url ON url.url_id = bookmark.url_id
…

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 ON condition.

inner

only returns entries that found a partner.

left

doesn't drop columns from the already present columns, leaving the joined columns NULL.

right

additionally outputs all entries from the joined table that didn't find a partner, leaving the other columns NULL.

full

like a left and right join, returning all of the entries in both tables, joining the ones that are join-able and filling in where joining isn't possible.

Join Examples

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 left_t and right_t for illustration purposes, bot with a single column.

CREATE TABLE left_t(lc);
CREATE TABLE right_t(rc);
INSERT INTO left_t VALUES ('both'),('left');
INSERT INTO right_t VALUES ('both'),('right');

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 right_t against 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:

both|both
left|

All rows in left_t table mad it through the join, the hole in the right_t was filled with a (blank) NULL field.

Note: If the right_t table had more columns all of them would be returned as NULL, independent of any DEFAULT or NOT NULL statements.

Similar results for a RIGHT join:

both|both
|right

Now all rows from the right_t table made it through and the missing rows in the left_t table was filled with a NULL entry.

And for the FULL join:

both|both
left|
|right

All rows preserved, the ones that matched got joined that ones without matches had the resulting holes filled with NULL fields.

Database Exploration Commands

You Probably know the problem, a Database you are not familiar with with an engine you don't use every day and what were the commands again?