Blog

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.

Symbol Operation != Not equal to <> Not equal to !> Not greater than !< Not less 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.

Symbol Operation & Bitwise and | Bitwise or ^ Bitwise xor

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.

Symbol Operation + Positive – Negative ~ Bitwise not

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.



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.

Symbol Operation + Addition – Subtraction * Multiplication / Division % Modulo

Assignment Operators

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

Symbol Operation = Assignment := Assignment

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;

Symbol Operation += Addition -= Subtraction *= Multiplication /= Division %= Modulo &= Bitwise and |= Bitwise or ^= Bitwise xor

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.

Symbol Operation << Bitshift left >> Bitshift right

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

Symbol Operation && Logical and || Logical or ! Logical not

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.

Symbol Operation + Concatenation || Concatenation += Concatenate and assign % Match 0 or more wildcard _ Match exactly 1 wildcard [] Escape special characters

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.

Symbol Operation (*) Count rows per group (1) Count rows per group

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

Symbol Operation :: Type conversion

Postgres Json

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

Symbol Operation -> Get json element ->> Get json element as text #> Get element by path #>> Get element by path as text @> Contains <@ Contains ? String exists ?| Any string exists ?& All strings exist

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.

Symbol Operation @ User session variable @@ System variable

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.

Symbol Operation — Inline comment # Inline comment /* Block comment */ Block comment

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

Symbol Operation (+) Outer join

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

Learn more about SQL analytics best practices, tips, and tricks here:

