SQL AND & OR - SQL Tutorial
The SQL AND clause is used when you want to specify more than one condition in
your SQL WHERE clause, and at the same time you want all conditions to be true.
For example if you want to select all customers with FirstName "John" and
LastName "Smith", you will use the following SQL expression:
|
SELECT * FROM Customers WHERE FirstName = 'John' AND LastName = 'Smith' |
The result of the SQL query above is:
| FirstName | LastName | DOB | Phone | |
| John | Smith | John.Smith@yahoo.com | 2/4/1968 | 626 222-2222 |
The following row in our Customer table, satisfies the second of the conditions (LastName = 'Smith'), but not the first one (FirstName = 'John'), and that's why it's not returned by our SQL query:
| FirstName | LastName | DOB | Phone | |
| James | Smith | jim@supergig.co.uk | 20/10/1980 | 416 323-8888 |
The SQL OR statement is used in similar fashion and the major difference compared to the SQL AND is that OR clause will return all rows satisfying any of the conditions listed in the WHERE clause.
If we want to select all customers having FirstName 'James' or FirstName 'Paula' we need to use the following SQL statement:
|
SELECT * FROM Customers WHERE FirstName = 'James' OR FirstName = 'Paula' |
The result of this query will be the following:
| FirstName | LastName | DOB | Phone | |
| Paula | Brown | pb@herowndomain.org | 5/24/1978 | 416 323-3232 |
| James | Smith | jim@supergig.co.uk | 20/10/1980 | 416 323-8888 |
You can combine AND and OR clauses anyway you want and you can use parentheses
to define your logical expressions.
Here is an example of such a SQL query, selecting all customers with LastName
'Brown' and FirstName either 'James' or 'Paula':
|
SELECT * FROM Customers WHERE (FirstName = 'James' OR FirstName = 'Paula') AND LastName = 'Brown' |
The result of the SQL expression above will be:
| FirstName | LastName | DOB | Phone | |
| Paula | Brown | pb@herowndomain.org | 5/24/1978 | 416 323-3232 |