SQL - And

SQL AND links together two or more conditional statements for increased filtering when running SQL commands. AND helps the developer query for very specific records while answering questions like, "I want to view all orders made by a certain customer AND made on a special date." There is no limit to the number of AND/OR conditions that can be applied to a query utilizing the WHERE clause. This makes it possible for the developer to be as precise as needed when querying for results.

SQL And Code:

USE mydatabase;

SELECT *
FROM orders
WHERE customer = 'Tizag'
AND day_of_order = '08/01/08'
AND product = 'Pen';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4

This example illustrates how SQL AND combines multiple conditional statements (3 total now) into a single condition with multiple circumstances (filters). Each filter removes rows from the result set that doesn't meet the condition.

SQL - Or

SQL OR also applies logic to help filter results. The difference is that instead of linking together conditional statements, an OR condition just asks SQL to look for 2 separate conditions within the same query and return any records/rows matching either of the conditions.

SQL Or Code:

USE mydatabase;

SELECT *
FROM orders
WHERE product = 'Pen'
OR product = '19" LCD Screen';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	3
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3

The first record returned matches the first condition since the product = 'Pen'. The two records after that match the other condition; the product in each of those orders is the '19" LCD Screen'. This type of logic allows the developer to filter results based on one or more conditions.
SQL AND allows the developer to query for more specific data by linking together conditional statements. On the other end of the spectrum, SQL OR creates a new, independent condition not linked to any other conditional statement.

SQL - And / Or Combination

Conditional statements can be grouped together using parentheses (). Doing so links together conditions and provides robust solutions for data querying.

SQL And/Or Code:

USE mydatabase;

SELECT *
FROM orders
WHERE (quantity > 2 AND customer = 'Tizag')
OR (quantity > 0 AND customer = 'Gerald Garner')

By encapsulating the first two conditions (quantity > 2 AND customer = 'Tizag') SQL now treats this as a single condition, and the same is true for the next line. These two conditions have been linked together with the OR operator, creating very unique behavior.
SQL is now looking for rows where the customer is Tizag AND the quantity is more than 2, and ALSO looking for rows where the customer is Gerald Garner AND the quantity is greater than 0. All rows meeting either condition will be returned as demonstrated in our results below.

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	3
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3

Posted in: SQL