# SQL Operators

# SQL Arithmetic Operators

You can only perform arithmetic across columns on values in a given row. To add values across multiple rows, you'll need to use aggregate functions (opens new window),

You can use parentheses to manage the order of operations (opens new window).

SELECT year,
       month,
       west,
       south,
       (west + south)/2 AS south_west_avg
  FROM tutorial.us_housing_units

Operator	Description	Example
+	Add	Try it (opens new window)
-	Subtract	Try it (opens new window)
*	Multiply	Try it (opens new window)
/	Divide	Try it (opens new window)
%	Modulo	Try it (opens new window)

# SQL Bitwise Operators

Operator	Description
&	Bitwise AND
\|	Bitwise OR
^	Bitwise exclusive OR

# SQL Comparison Operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

# Comparison operators on non-numerical data

Rules when using these operators

If you're using an operator with values that are non-numeric, you need to put the value in single quotes: 'value'. Note: SQL uses single quotes to reference column values.
>, <, etc. filter based on alphabetical order

SELECT *
  FROM tutorial.us_housing_units
 WHERE month_name > 'J'

selecting month_name > 'J' will yield only rows in which month_name starts with "j" or later in the alphabet.

SQL considers 'Ja' to be greater than 'J' because it has an extra letter. It's worth noting that most dictionaries would list 'Ja' after 'J' as well.

# SQL Compound Operators

Operator	Description
+=	Add equals
-=	Subtract equals
*=	Multiply equals
/=	Divide equals
%=	Modulo equals
&=	Bitwise AND equals
^-=	Bitwise exclusive equals
\|*=	Bitwise OR equals

# SQL Logical Operators

Aallow you to use multiple comparison operators in one query.

Operator	Description	Example
`ALL`	TRUE if all of the subquery values meet the condition	Try it (opens new window)
`AND` (opens new window)	TRUE if all the conditions separated by AND is TRUE	Try it (opens new window)
`ANY`	TRUE if any of the subquery values meet the condition	Try it (opens new window)
`BETWEEN` (opens new window)	TRUE if the operand is within the range of comparisons. Has to be paired with the `AND` (opens new window) operator.	Try it (opens new window)
`EXISTS`	TRUE if the subquery returns one or more records	Try it (opens new window)
`IN` (opens new window)	TRUE if the operand is equal to one of a list of expressions	Try it (opens new window)
`LIKE` (opens new window)	TRUE if the operand matches a pattern	Try it (opens new window)
`NOT` (opens new window)	Displays a record if the condition(s) is NOT TRUE	Try it (opens new window)
`OR` (opens new window)	TRUE if any of the conditions separated by OR is TRUE	Try it (opens new window)
`SOME`	TRUE if any of the subquery values meet the condition	Try it (opens new window)
`IS NULL` (opens new window)	to select rows that contain no data in a given column.

# The SQL LIKE operator

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE "group" LIKE 'Snoop%'

# The SQL IN operator

Allows you to specify a list of values that you'd like to include in the results.

# SQL BETWEEN operator

BETWEEN is a logical operator (opens new window) in SQL that allows you to select only rows that are within a specific range. It has to be paired with the AND (opens new window) operator,

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank BETWEEN 5 AND 10

is the same

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank >= 5 AND year_rank <= 10

# IS NULL operator

allows you to exclude rows with missing data from your results.

You can select rows that contain no data in a given column by using IS NULL.

WHERE artist = NULL will not work—you can't perform arithmetic on null values.

# SQL AND operator

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2012 AND year_rank <= 10

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2012
   AND year_rank <= 10
   AND "group" ILIKE '%feat%'

# SQL OR

to select rows that satisfy either of two conditions.

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank = 5 OR artist = 'Gotye'

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
   AND ("group" ILIKE '%macklemore%' OR "group" ILIKE '%timberlake%')

# SQL NOT

NOT is a logical operator (opens new window) that you can put before any conditional statement to select rows for which that statement is false.

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
   AND year_rank NOT BETWEEN 2 AND 3

NOT is commonly used with LIKE.

NOT is also frequently used to identify non-null rows

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
   AND artist IS NOT NULL

← SQL Delete SQL Relations →