###### SQL 101

# SQL Cheat Sheet & Query Syntax

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

**, this guide is for you.**

*where users.group_id(+) = group.id*### 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

**whereas**

*1***returns**

*168 & 1***.**

*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 division, ** 9 / 2 = 4**, to get the remainder you can use

**, which equals**

*9 % 2***. 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:**

*1***and**

*5 % 3 = 2***, but**

*5 % -3 = 2***and**

*-5 % 3 = -2***.**

*-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

**. You move the bits**

*20***to**

*101***, which is the same result as multiplying by four.**

*10100*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 ** and**,

**and**

*or***in conditionals. You can replace**

*not*whereuser_id<10andnotgroup_id=3

with

whereuser_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**

*||***operator,**

*like***and**

*%***act as wildcard match characters. For example Periscope matches**

*_***and**

*P_r_scope***, while only the latter would match Periscope Data. You can read a more in-depth introduction to strings here.**

*%scope%*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,

**for repetition, and enclosing ranges of characters in brackets:**

*{2}***.**

*[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

**in queries. They are interchangeable, and both count the number of rows being selected.**

*count(*)*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 ** count**s here will return different quantities.

select

count(*)

,count(groups.*)

from

users

leftjoingroups

onusers.group_id=groups.id

### Type Conversion

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

selectcast('2016-12-25'asdate)aschristmas

becomes

select::date'2016-12-25'aschristmas

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*

fromusers, groups

whereusers.group_id(+)=groups.id

behaves similarly to

select*

fromusersouterjoingroups

onusers.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!

Be sure to check also the SQL cheat sheet of our partner, Sisense.