A short video covering simple WHERE clauses within a SQL SELECT statement.
For beginners. Covers the comparison operators, and logical expressions using NOT, AND, and OR.
Here is the SQL that goes with the demo.
Use Northwind
SELECT 'FRED' AS [firstName],
23 AS [age]
-- SELECT statements always returns a table
-- the SELECT clause determines the columns of the table
SELECT *
FROM Products
-- so the * 'wildcard' means 'all columns'
-- the above statement says 'create a table with a column for every column in the Products table'
SELECT ProductID
FROM Products
-- in this next statement, I am re-ordering the columns
SELECT ProductName,
ProductID,
unitprice
FROM Products
-- we are seeing all rows from the products table
-- note the ordering of the rows
-- to specify the order of the rows, we need a new clause: ORDER BY
SELECT Productname,
ProductID,
unitprice
FROM Products
ORDER BY ProductID ASC
-- or
SELECT Productname,
ProductID,
Unitprice
FROM Products
ORDER BY UnitPrice DESC
--
-- at this point, we can control:
-- which columns with the SELECT clause
-- the *order* of the columns with the SELECT clause
-- the source of the data with the FROM clause
-- the *order* of the rows
-- but we haven't yet specified *which* rows
-- we need the WHERE clause for that
SELECT Productname,
ProductID,
Unitprice
FROM Products
WHERE unitprice < 8
ORDER BY UnitPrice DESC
-- so here's the general form of the SELECT statement so far:
--SELECT
--FROM
--WHERE
--ORDER BY
-- SELECT and ORDER BY take lists of expressions
-- whereas FROM and WHERE take expressions
-- here's what I mean
SELECT LEFT(ProductName, 3),
unitprice * 2.2
FROM Products
ORDER BY ProductName,
SQRT(unitprice) DESC
-- see how the items in SELECT and ORDER BY are lists - they have commas
-- and they can be *expressions*
SELECT Productname,
ProductID,
Unitprice
FROM Products
WHERE unitprice < 8
ORDER BY UnitPrice DESC
-- this part is an expression: unitprice < 8
-- furthermore, it's a *logical* expression
-- it is either true or false (zero or one)
SELECT unitprice,
CONVERT(bit, CASE WHEN unitprice < 8 then 1 else 0 end) AS [cheap product]
FROM Products
ORDER BY unitprice asc
-- don't pay attention to the CONVERT... expression
-- but notice, I can *test* whether or not a unitprice is less than 8
-- now the same thing, but only show the cheap products
SELECT unitprice,
CONVERT(bit, CASE WHEN unitprice < 8 then 1 else 0 end) AS [cheap product]
FROM Products
WHERE unitprice < 8
ORDER BY unitprice asc
-- what about other ways?
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE unitprice >= 10
ORDER BY ProductID
-- comparison operators: >, >=, <, <=, =
-- and 'not equal to' can be <> or !=
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE ProductName = 'Chai'
ORDER BY ProductID
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE ProductName > 'Chai'
ORDER BY ProductID
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE ProductName <> 'Chai'
ORDER BY ProductID
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE discontinued = 1
ORDER BY ProductID
-- with an embedded expression
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE (Unitprice)/2.0 > 5
ORDER BY ProductID
-- (Unitprice)/2.0 is a mathematical expression that evaluates as a number
-- (Unitprice)/2.0 > 5 is a logical expression that evaluates as 'true' or 'false'
-- now for some logical operators: NOT, AND, OR
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE discontinued = 1
ORDER BY ProductID
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE NOT (discontinued = 1)
ORDER BY ProductID
-- combine two logical expression with AND
-- makes a bigger logical expression
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE discontinued = 0
AND unitprice > 5
ORDER BY ProductID
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE discontinued = 0
OR unitprice > 5
ORDER BY ProductID
-- and you can control the order of evaluation with parentheses
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE NOT discontinued = 0 AND unitprice > 5
ORDER BY ProductID
-- which is the same as
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE (NOT discontinued = 0) AND unitprice > 5
ORDER BY ProductID
-- which is different than
SELECT ProductID,
unitprice,
ProductName,
discontinued
FROM Products
WHERE NOT (discontinued = 0 AND unitprice > 5)
ORDER BY ProductID
-- that's simple WHERE clauses
