Periscope Data
The world’s fastest visualization platform for data analysts.

SQL Symbol Cheat Sheet

September 16, 2016

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 whether 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 encounted % before in an algebraic context. Modulo divides it’s left side value by it’s right side value and returns the remainder. It’s a companion operator for division, which returns the quotient.

In integer division9 / 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 sql 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 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 andor 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 Periscope matches P_r_scope and %scope%, while only the latter would match Periscope Data. 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.

If you are searching for the meaning of a character symbol and can’t find it on our list, we’d love to know @PeriscopeData!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?