SQL Operators

SQL operators enhance the power of the WHERE clause by enabling advanced filtering and calculations in your queries. In this lesson, you’ll learn how to use logical, arithmetic, and comparison operators in SQL to extract and manipulate data effectively.

Logical Operators

Logical operators are used to combine conditions or evaluate the truth of a statement. Here’s a list of common logical operators:

Operator	Description
`ALL`	TRUE if all subquery values meet the condition.
`AND`	TRUE if all conditions are TRUE.
`ANY`	TRUE if any subquery values meet the condition.
`BETWEEN`	TRUE if a value is within a specified range.
`EXISTS`	TRUE if the subquery returns one or more records.
`IN`	TRUE if a value matches any value in a list.
`LIKE`	TRUE if a value matches a specified pattern.
`NOT`	TRUE if the condition is NOT TRUE.
`OR`	TRUE if any condition is TRUE.
`SOME`	TRUE if any subquery values meet the condition.

Example: Combining Logical Operators

Here’s a query using NOT, IN, AND, and LIKE:

SELECT name, breed 
  FROM puppies 
  WHERE breed NOT IN ('Miniature Schnauzer', 'Basset Hound', 'Labradoodle') 
    AND breed NOT LIKE '%Shepherd';

Result:

name     breed
Indie    Yorkshire Terrier
Zoe      Korean Jindo
Callie   Corgi
Jaxson   Beagle

This query excludes specific breeds and filters out Shepherd breeds using the LIKE operator with a wildcard (%).

LIKE Wildcard Patterns

The LIKE operator uses wildcards for pattern matching:

Pattern	Matches	Example
`'dog'`	Exact match.	Matches "dog" only.
`'%dog'`	Ends with "dog".	Matches "hotdog".
`'dog%'`	Starts with "dog".	Matches "dog-tired".
`'%dog%'`	Contains "dog".	Matches "ordogordo".

Arithmetic Operators

Arithmetic operators perform calculations in your queries. Common operators include:

Operator	Meaning	Example
`+`	Addition	`a + b`
`-`	Subtraction	`a - b`
`*`	Multiplication	`a * b`
`/`	Division	`a / b`
`%`	Modulus	`a % b`

Example: Multiplication

Find puppies that are 6 months old:

SELECT name, breed, age_yrs 
  FROM puppies 
  WHERE age_yrs * 10 = 6;

Result:

name       breed          age_yrs
Ladybird   Labradoodle    0.6

Comparison Operators

Comparison operators allow you to compare values. Common operators include:

Operator	Meaning	Example
`=`	Equals	`a = b`
`!=`	Not equal to	`a != b`
`<>`	Not equal to	`a <> b`
`>`	Greater than	`a > b`
`<`	Less than	`a < b`

Example: Greater Than

Find puppies weighing more than 50 lbs:

SELECT name, breed, weight_lbs 
  FROM puppies 
  WHERE weight_lbs > 50;

Result:

name   breed             weight_lbs
Max    German Shepherd    65

Complete Query

A complete query may include multiple clauses. For example:

SELECT column1, column2 
  FROM table 
  WHERE condition 
  ORDER BY column ASC 
  LIMIT num OFFSET num;

What You Learned

In this lesson, you learned how to use:

Logical operators: Combine or filter conditions.
Arithmetic operators: Perform calculations in queries.
Comparison operators: Compare values in conditions.

Real-World Applications

SQL operators are essential for:

Data analysis: Performing calculations and filtering data.
Web applications: Searching, sorting, and filtering user data.
Reporting: Generating detailed and custom reports.

With these skills, you can build powerful and efficient SQL queries for a variety of use cases!