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	Email	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	Email	DOB	Phone
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

SQL OR

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	Email	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	Email	DOB	Phone
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232

SQL IN

The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria. THE SQL IN syntax looks like this:

SELECT Column1, Column2, Column3, … FROM Table1 WHERE Column1 IN (Valu1, Value2, …) ;

Lets use the EmployeeHours table to illustrate how SQL IN works:

 

Employee	Date	Hours
John Smith	5/6/2004	8
Allan Babel	5/6/2004	8
Tina Crown	5/6/2004	8
John Smith	5/7/2004	9
Allan Babel	5/7/2004	8
Tina Crown	5/7/2004	10
John Smith	5/8/2004	8
Allan Babel	5/8/2004	8
Tina Crown	5/8/2004	9

Consider the following SQL query using the SQL IN clause:

SELECT * FROM EmployeeHours WHERE Date IN ('5/6/2004', '5/7/2004');

This SQL expression will select only the entries where the column Date has value of '5/6/2004' or '5/7/2004', and you can see the result below:

We can use the SQL IN statement with another column in our EmployeeHours table:

SELECT * FROM EmployeeHours WHERE Hours IN (9, 10);

The result of the SQL query above will be:

SQL BETWEEN

The SQL BETWEEN keyword define a range of data between 2 values. The SQL BETWEEN syntax looks like this:

SELECT Column1, Column2, Column3, … FROM Table1 WHERE Column1 BETWEEN Value1 AND Value2 ;

The values defining the range for SQL BETWEEN clause can be dates, numbers or just text. It gives you the ability to specify a range in your search criteria. We are going to use the Customers table to show how SQL BETWEEN works:

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
Steven	Goldfish	goldfish@fishhere.net	4/4/1974	323 455-4545
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

Consider the following SQL BETWEEN statement:

SELECT * FROM Customers WHERE DOB BETWEEN '1/1/1975' AND '1/1/2004' ;

The SQL BETWEEN statement above will select all Customers having DOB column between '1/1/1975' and '1/1/2004' dates. Here is the result of this SQL expression: