SQL

Compound Searches With SQL Using AND && OR

Previously, we looked at the SQL SELECT and WHERE options to obtain output and then constrain what would be displayed in a query. The AND & OR operators are used to add additional logic so you can filter records based on more than one condition. Which is to say to search based on the contents of multiple columns within a single table. AND is used to show information if two different conditions are true and OR is used to show information if either condition is true.

Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

The following example SQL statement outputs customers from the country “US” AND the city “55418”, in the “Customers” table from above:

SELECT * FROM Customers
WHERE Country='US'
AND Zip='55418';

The following SQL statement selects all customers from the city “Minneapolis” OR the city “Cupertino”, in the “Customers” table:

SELECT * FROM Customers
WHERE City='Cupertino'
OR City='Minneapolis';

As the logic of your searches expands, you can combine AND and OR by nesting logic within parenthesis (who said pre-algebra would be useless?!?!). For example, the following SQL statement selects all customers from the country “US” AND the city must be equal to “Minneapolis” OR “Cupertino”, in the “Customers” table:

SELECT * FROM Customers
WHERE Country='US'
AND (City='Minneapolis' OR City='Cupertino');