SQL cheat sheet & query syntax

SQL is one of the analyst’s most powerful tools. In SQL Superstar, we give you actionable advice to help you get the most out of this versatile language and create beautiful, effective queries.

When you are new to programming in SQL, you will come across a lot of hard-to-search-for character operators. If you are prone to forgetting ~ is called tilde, are wondering why there are so many %s in your strings, or have a hard time googling what the (+) symbol in where users.group_id(+) = group.id, this guide is for you.

Comparison Operators

You are well acquainted with the equality and inequality operators for equals-to, less-than, and greater-than being =, <, and >, but you might not have seen all of the variants for specifying not-equals-to, not-less-than, and not-greater-than.

While some databases like sql-server support not less than and not greater than, they do not support the analogous not-less-than-or-equal-to operator !<=.

Unary and Bitwise Operators

When working with structured numbers, like IP addresses, it can be helpful to extract specific digits from the number using bitwise operations. Numbers are stored using binary, and you can think of the 1s being true and the 0s being false, and apply boolean algebra to manipulate the numbers.

There is an additional bitwise operator among the unary operators + and – for defining a positive or negative number. Bitwise not, ~, inverts each of the bits in your number.

Suppose you wanted to know if a number was even or odd. You could divide by two, take the modulo of two, or other arithmetic strategies, but it can be computationally faster to just look at the last bit. 505 & 1 returns 1 whereas 168 & 1 returns 0.

Get SQL tips and tricks from our experts:

Read More

Arithmetic Operators

The arithmetic operators +, -, *, / for addition, subtraction, multiplication, and division are old friends, but you might not have encountered % before in an algebraic context. Modulo divides its left side value by its right side value and returns the remainder. It’s a companion operator for division, which returns the quotient.

In integer division, 9 / 2 = 4, to get the remainder you can use 9 % 2, which equals 1. The definition breaks down when working with negative numbers, but you will get an answer. For most SQL variants, an easy trick is it will inherit the sign of the left operator: 5 % 3 = 2 and 5 % -3 = 2, but -5 % 3 = -2 and -5 % -3 = -2.

Assignment Operators

Some SL languages, like SQL Server, use = for assignment, while others like MySQL and Postgres use :=.

In SQL Server, you can use most of the bitwise or arithmetic operators to assign the computed value back into the left operand in a compound assignment. For example:

SET @x1 = @x1 + 7;

can be re-written as:

SET @x1 += 7;

Bitshifting

Another way to manipulate bits in SQL is using arithmetic bitshifting. This operation “moves” the bits in your number either left or right and fills in the new values with 0s. Bitshifting left is an easy way to multiply by powers of 2, and Bitshifting right divides by powers of 2. For example, 5 << 2 equals 20. You move the bits 101 to 10100, which is the same result as multiplying by four.

Numbers in SQL are limited to a fixed number of bits. An integer type, for example, will only have 32 bits, and if you bitshift a bit past the 32nd value, it is dropped from the number. Generally, if you bitshift by a number larger than 32, it uses the modulo: 7 << 34 behaves like 7 << 2.

Boolean logic

MySQL let’s you substitute the character equivalents for and, or, and not in conditionals. You can replace

where user_id < 10 and not group_id = 3

with

where user_id < 10 && ! group_id = 3

String comparisons and manipulation

Working with strings can be unfamiliar whether you are new to programming or just new to SQL. Some SQL variants use the familiar + to concatenate strings, while others like MySQL use ||. When comparing strings using the like operator, % and _ act as wildcard match characters. For example, “Data” matches D_t_ and %at%, while only the latter would match Data Teams. You can read a more in-depth introduction to strings here.

There is an additional difference between strings quoted using a single quote mark (‘) and a double quote (“). String literals are in ‘single quotes’ and “double quotes” denote identifiers, like table and column names.

Regular Expressions

Regular expressions are filled with esoteric characters, featuring special uses for a wide range of characters, including ? for optional matching, {2} for repetition, and enclosing ranges of characters in brackets: [A-Za-z0-9].

For help deciphering the ‘.*([0-9]+)[ _-]?ye?a?rs( old)?’ regular expression in SQL, check out this blog post.

Count

You may see both count(1) and count(*) in queries. They are interchangeable and both count the number of rows being selected.

You may run into the occasional case where the count is being applied to a specific table — in these cases it counts the fields, not the rows in the table. We recommend you pick one and stick with it — there is no semantic or performance difference between the two formats. Note that the two counts here will return different quantities.

select 
    count(*)
    , count(groups.*) 
from 
  users 
  left join groups 
    on users.group_id = groups.id

Type Conversion

In addition to using cast, you can use :: to cast a value to a specific type.

select cast('2016-12-25' as date) as christmas

becomes

select '2016-12-25'::date as christmas

Postgres Json

Postgres has great support for json types, including a slew of character operators to extract data out of a json blob.

Local and Global variables

When defining variables in MySQL, they may be prefixed with @ or @@. A single @ lets a user-defined variable persist beyond the query that defined the variables, and @@ is a reference to global state, such as @@global.port.

Oracle uses @ for running scripts. You can check out more here.

Comments

You can use comments to include text that won’t get evaluated as part of your sql query. The standard set of comments tokens are –, /*, */, and MySQL additionally uses # for inline comments.

Oracle outer join

A legacy operator for older Oracle databases is the use of (+) to support outer joins.

select *
from users, groups
where users.group_id(+) = groups.id

behaves similarly to

select *
from users outer join groups
on users.group_id(+) = groups.id

It largely behaves the same as outer join, but there are some exceptions you can check out in Oracle’s language reference.

Get SQL tips and tricks from our experts:

Read More

Chris Meier is a Manager of Analytics Engineering for Sisense and boasts 8 years in the data and analytics field, having worked at Ernst & Young and Soldsie. He’s passionate about building modern data stacks that unlock transformational insights for businesses.