Home >SQL Server >SQL Tutorial

SQL WHERE Clause

Hi

In this tutorial I will explain SQL Where clause, how to use it and give you SQL Statement examples. WHERE is used to filter data that you extract based on the conditions your specify.

SQL WHERE syntax

Below is the basic example of SQL Where syntax:

SELECT Field1, Field2
FROM TableName
WHERE FieldName = Value

Before we go into an example let's clarify condition in where clause. Condition is evaluated to either TRUE or FALSE. So when you retrieve the data SQL will evaluate condition and it will include only rows where condition is TRUE. Things are slightly more complicated when you use multiple conditions but the principle stays the same and I will go into more details with multiple conditions.

Below are examples of simple condition:

SELECT *
FROM Customer
WHERE 2 = 2

2 = 2 is always TRUE so in our case our select will return all rows

SELECT *
FROM Customer
WHERE 1 = 2

1 = 2 is always FALSE so our SELECT will return 0 data rows.

Now that I explain what a condition is let's have a look into real life example using one condition:

SQL WHERE Clause

In this case we have filtered the results and we shows only customers (records) with surname = 'Glownia'. See below more rules regarding data types:

Data Type Rules:
String = if you field is string data type then you will have to enlose your values using single quotes like that 'value'. If you have values with single quote like surname al'jar then you will have to use double single quote for the quote inside like that 'al''jar'
Numeric = for numeric values we don't need quotes so we can just do something like that Year = 2011
Date = Dates require quotest and you need to use specific format which is YYYY-MM-dd so if you want order that have been places on 1st of October 2011 then you can use OrderDate = '2011-10-01' but BEWARE! Field can contain time so you may lose rows! It is usually good idea to use function in this case like that DATEDIFF(d,'2011-10-06 08:00:00','2011-10-06 16:00:00') = 0 you can notice that the time is different but I use datediff function to ignore time.
Boolean = Which is basically TRUE and FALSE in sql server we use 0 = FALSE and 1 = TRUE
NULL = this is special value that represent non-existend value and we can filter it using IS NULL for instance email is null. Beware that you can also have empty string which different to NULL and you filter empty string (for string data type) using the following example email = ''

SQL WHERE examples

Soon.... I will update more examples

Take care
Emil

News & Updates: Follow @katieglownia Follow @emilglownia

You like it? Share it!

Comments Add Comment
No data was returned. Shere your thoughts, questions and suggest improvements:
Add Comment