SQL WHERE Clause

The SQL WHERE clause is used to extract / filter specific table records(rows).

It is used to specify a condition (one or more) at once while extracting / fetching the data from one or more tables.

If the given condition is matched successfully then only it returns filtered data or records from the table and which you expected to see.

The SQL WHERE clause is used in SELECT, UPDATE, INSERT, and DELETE statements.

If the column used in the WHERE clause has non-null values, the SQL IS NOT NULL operator is used to filter the output.

IS NULL and IS NOT NULL are SQL operators that are used in conjunction with the WHERE clause in SELECT, UPDATE, and DELETE statements/queries to determine whether a column has data or not. Please remember that NULL and 0 are not synonymous. A column with a NULL value is empty and has no value.

Note: IS NULL and IS NOT NULL can be used in the WHERE clause of the same SQL query in any order and combination as needed.

Syntax for IS NOT NULL

SELECT column-names
 FROM table-name
 WHERE column-name IS NOT NULL

Example 1:

SELECT *
FROM Employees
WHERE ManagerId IS NOT NULL

This statement will return all Employee records where the value of the ManagerId is not NULL.

Output:

Id    FName       LName     PhoneNumber    ManagerId    DepartmentId
2     John        Johnson   2468101214     1            1
3     Michael     Williams  1357911131     1            2
4     Johnathon   Smith     1212121212     2            1

Note: If you update the WHERE clause to WHERE ManagerId = NULL or WHERE ManagerId <> NULL, the query will no longer return results.

Example 2: USE IS NOT NULL IN WHERE CLAUSE IN SELECT QUERIES:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age IS NOT NULL;

Only records with an Age value that is not NULL are selected by this SQL. The rest of the records aren't chosen.

Example 3: Problem: List all suppliers with a fax.

SELECT Id, CompanyName, Phone, Fax 
 FROM Supplier
 WHERE Fax IS NOT NULL

Example 3: Let’s consider the earlier defined Supplier table for example purpose.

To Get the name of the product whose supplier name is not null.

SELECT ProductName FROM Supplier WHERE SupplierName IS NOT NULL;

Output:

SupplierName
Cookie
Jam

The AND and OR operators are used in SQL to filter data and produce exact results based on requirements. Multiple conditions can also be combined using the SQL AND and OR operators. In a SELECT, INSERT, UPDATE, or DELETE statement, these two operators can be coupled to test for numerous conditions.

It's necessary to use parenthesis when merging these conditions so the database knows what order to analyze them in.

• The WHERE clause employs the AND and OR operators.
• Conjunctive operators refer to these two operators.

Syntax:

The syntax for the AND condition and OR condition together in SQL is:

WHERE condition1
AND condition2
...
OR condition_n;

Using the "AND" and "OR" Conditions with the SELECT Statement.

Example 1: Now, let's look at an example of how to use the AND condition and OR condition together in a SELECT statement.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name		city			state
100		Microsoft		Redmond			Washington
200		Google			Mountain View		California
300		Oracle			Redwood City		California
400		Kimberly-Clark		Irving			Texas
500		Tyson Foods		Springdale		Arkansas
600		SC Johnson		Racine			Wisconsin
700		Dole Food Company	Westlake Village	California
800		Flowers Foods		Thomasville		Georgia
900		Electronic Arts		Redwood City		California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE (state = 'California' AND supplier_id <> 900)
OR (supplier_id = 100);

There will be 4 records selected. These are the results that you should see:

supplier_id	supplier_name		city			state
100		Microsoft		Redmond			Washington
200		Google			Mountain View		California
300		Oracle			Redwood City		California
700		Dole Food Company	Westlake Village	California

This example returns all suppliers who are located in California but do not have a supplier id of 900. All suppliers with a supplier id of 100 will also be returned by the query. The sequence in which the AND and OR criteria are evaluated is determined by the parenthesis. Just as you learnt in math class about the order of operations!

Example 2: Take another example, to fetch all the records from the Student table where NAME is Ram and Age is 18.

SELECT * FROM Student WHERE Age = 18 AND NAME = 'Ram';

Output:

ROLL_NO	NAME	ADDRESS	PHONE		Age
1	Ram	Delhi	XXXXXXXXXX	18

To fetch all the records from the Student table where NAME is Ram or NAME is SUJIT.

SELECT * FROM Student WHERE NAME = 'Ram' OR NAME = 'SUJIT';

Output:

ROLL_NO	NAME	ADDRESS	PHONE		Age
1	Ram	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
3	SUJIT	ROHTAK	XXXXXXXXXX	20

Example 3: SQL AND & OR Operators together

Table: EMPLOYEE

+----+--------------+-----+-----------+----------+
| ID | EMP_NAME     | AGE | LOCATION  | SALARY   |
+----+--------------+-----+-----------+----------+
|123 | Daryl        |  41 | Chennai   |  120000  |
|124 | Jon          |  40 | Delhi     |   80000  |
|125 | Saru         |  43 | Agra      |  110000  |
|126 | Hemant       |  39 | Shimla    |  110000  |
|127 | Devesh       |  42 | Goa       |   90000  |
+----+--------------+-----+-----------+----------+

The following SQL statement will fetch the details of those employees, who are more than 40 years old and their location is either “Chennai” or “Agra”.

SELECT * FROM EMPLOYEE
WHERE AGE>40 AND (LOCATION='Chennai' OR LOCATION='Agra');

Result:

+----+--------------+-----+-----------+----------+
| ID | EMP_NAME     | AGE | LOCATION  | SALARY   |
+----+--------------+-----+-----------+----------+
|123 | Daryl        |  41 | Chennai   |  120000  |
|125 | Saru         |  43 | Agra      |  110000  |
+----+--------------+-----+-----------+----------+

The SQL Between statement chooses values from a specified range of values. With the where condition, the values can be numbers, text, or dates.

In a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilised.

The SQL Between condition returns entries in which expression falls between value1 and value2.

The SQL Between operator is similar to SQL IN operators when used sequentially.

The BETWEEN operator looks for all values in the range, including those that are between and equal to the lower and higher border values. The BETWEEN operator is conceptually equivalent to two separate comparisons linked by the AND Boolean operator.

Syntax:

Syntax of BETWEEN operator with WHERE in SQL

SELECT column_Name1, column_Name2 ...., column_NameN 
 FROM table_Name WHERE column_name BETWEEN value1 and value2 ;  

If the value of column_name is more than or equal to value1 and less than or equal to value2, the BETWEEN operator returns True.

Example 1: Using BETWEEN Condition with Numeric Values

Let's look at an example of how to use the BETWEEN condition to retrieve values within a numeric range.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name		city			state
100		Microsoft		Redmond			Washington
200		Google			Mountain View		California
300		Oracle			Redwood City		California
400		Kimberly-Clark		Irving			Texas
500		Tyson Foods		Springdale		Arkansas
600		SC Johnson		Racine			Wisconsin
700		Dole Food Company	Westlake Village	California
800		Flowers Foods		Thomasville		Georgia
900		Electronic Arts		Redwood City		California

Enter the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 300 AND 600;

Output:

supplier_id	supplier_name	city		state
300		Oracle	Redwood City		California
400		Kimberly-Clark	Irving		Texas
500		Tyson Foods	Springdale	Arkansas
600		SC Johnson	Racine		Wisconsin

Example 2: Get the names and budgets for all projects with a budget between $95,000 and $120,000, inclusive:

USE sample;
SELECT project_name, budget
FROM project
WHERE budget BETWEEN 95000 AND 120000;

Output:

project_name	budget
Apollo		120000
Gemini		95000

Example 3: Let's take the following STUDENT table, which helps you to understand the BETWEEN operator:

Roll_No	Name	Marks	Age
1	Raman	95	20
2	Kapil	92	19
3	Arun	85	17
4	Ram	92	18
5	Suman	55	20
6	Sanjay	88	18
7	Sheetal	65	19
8	Rakesh	64	20

Suppose, we want to fetch the record of those students from the below student table whose 'marks' are greater than or equal to 80 and less than or equal to 100. For this operation, we have to perform the following query in Structured Query Language:

SELECT * FROM STUDENT WHERE Marks BETWEEN 80 and 100 ;  

Output:

Roll_No	Name	Marks	Age
1	Raman	95	20
2	Kapil	92	19
3	Arun	85	17
4	Ram	92	18
6	Sanjay	88	18

It is quite simple to ignore the case in a where clause.

SQL is case-insensitive by default; however, you can create a case-sensitive SQL Server database and even make certain table columns case-sensitive. Check the "COLLATION" property of a database or database object and look for "CI" or "CS" in the result.

Example: convert both sides of the comparison to all caps notation:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

In general, NULL data denotes data that does not exist, is missing, or is unknown. IS NULL and IS NOT NULL are SQL operators that are used with a WHERE clause in SELECT, UPDATE, and DELETE statements/queries to determine whether a column has data or not. Please keep in mind that NULL and 0 are not equivalent.

Example 1: Let's use the identical scenario that we saw earlier. We'll look for not null values in this case.

Table: Employees

EmployeeName   EmployeeAge  EmployeePhoneNo  EmployeeAddress
------------   -----------  ---------------  ---------------
Cindi           34           95XXXXXXX8        Noida
Linda           35                             Agra
Shaun           33           75XXXXXXX4        Delhi
Timmy           34                             Noida
Pappu           36                             Noida

The EmployeeName and EmployeeAddress data of employees whose EmployeePhoneNo field is not null will be retrieved using the SQL statement below.

SELECT EmployeeName, EmployeeAddress
FROM Employees
WHERE EmployeePhoneNo IS NOT NULL;

Result:

EmployeeName   EmployeeAddress
------------   ---------------
Cindi           Noida
Shaun           Delhi

Example 2: USE IS NULL IN WHERE CLAUSE IN SELECT QUERIES:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age IS NULL;

This SQL only selects records with a NULL Age value. The rest of the records aren't selected.

Example 1: SQL WHERE clause contain string

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

Otherwise,

SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'

And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' or any other combination of the three.

To count how many rows a SELECT command returns, use the Count() method. The SELECT statement's Count Function with the WHERE clause displays records that fit the provided condition.

The COUNT(*) function returns the total number of rows returned by a SELECT statement, including duplicates and NULLs.

Syntax:

The syntax for the COUNT function in SQL is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Example 1: Let's look at an example of how to utilize the COUNT function in a query with a single expression.

In this example, we have a table called employees with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

SQL QUERY:

SELECT COUNT(*) AS total
FROM employees
WHERE salary > 50000;

Output:

total
3

In this case, we'll get the number of employees with a salary of more than $50,000. To make our query results more accessible, we've aliased COUNT(*) as total. When the result set is returned, total will now appear as the column heading.

Example 2: The following SQL statement counts the number of orders from "CustomerID"=7 from the "Orders" table:

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

Example 3: The two examples below will show you how to utilize the Count function in a SQL query with the WHERE clause:

In this example, we have the following Bike table with three columns:

Bike_Name	Bike_Color	Bike_Cost
Apache		Black		90,0000
Livo		Black		NULL
KTM RC		Red		185,000
KTM DUKE	White		NULL
Royal Enfield	Red		80,000
Pulsar 		Black		195,000

Suppose, you want to count the total number of bikes whose color is black. For this, you have to type the following statement in SQL:

SELECT COUNT (Bike_Name) AS TotalBikeBlackColor FROM Bikes WHERE Bike_Color = 'Black';

Output:

TotalBikeBlackColor
3

Example 4:

SELECT 
   COUNT(*) 
FROM 
   table_name
WHERE
   condition;

PostgreSQL has to scan the entire table sequentially when you use the COUNT(*) function on it.

Example 1: sql count value greater than:

SELECT COUNT(1) AS NumberOfGreaterThan0 
FROM YourTable
WHERE col1 >=0

Example 2: Have the information on customers that shopped in more than one way, such as online and in-store.

Observe the below query for the solution, including JOINs.

SELECT 
    cd1.*
FROM
    (SELECT 
        customer_id,
            COUNT(DISTINCT mode_of_shopping) AS countOfModes
    FROM
        customer_data
    GROUP BY customer_id) AS cd2,
    customer_data cd1
WHERE
    cd2.countOfModes > 1
        AND cd1.customer_id = cd2.customer_id
ORDER BY cd1.customer_name;

Using the BETWEEN clause, retrieve the date that falls between two dates. It can obtain values from a specified date range.

Example 1: date between two dates using an example.

SELECT name,start_date FROM employee
WHERE start_date BETWEEN '2000-01-01' AND '2006-01-01';

In the above output, we have shown the number of employees who started job between 2000-01-01 and 2006-01-01, as a result, we found that there are 9 employees.

Example 2: Let’s understand through an example.

SELECT name,end_date as left_date FROM employee
WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01';

The BETWEEN clause in the above code will display the names of employees who left the organisation on the day inclusively, that is, until the date specified in the BETWEEN clause, such as '2016-08-01'.

In other words, it will display the name of every person who left the organisation between January 7, 1998, and August 1, 2016.

Because this query is comprehensive, Dorthy Hamil left the firm on August 1, 2016.

Example 3: mysql date between two dates sql using where clause:

SELECT * FROM `objects` 
WHERE  (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

or

WHERE 
   requireddate BETWEEN 
     CAST('2003-01-01' AS DATE) AND 
     CAST('2003-01-31' AS DATE);

Example 4: The BETWEEN condition is used in the following date example to get values inside a date range.

SELECT *
FROM employees
WHERE start_date BETWEEN '2014/05/01' AND '2014/05/31';

Example 5: List All Dates – Including Start and End Date:

DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = '2021-11-01', @EndDate = '2021-12-01'; 
WITH ListDates(AllDates) AS
(    SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)
SELECT AllDates
FROM ListDates
GO

Example 1: MySQL where date greater than today:

Get the details of patient appointment where appointment_date is after today:

SELECT * FROM patient_appointment_details WHERE DATE(appointment_date) > CURDATE();

Example2: MySQL where date greater than yesterday

Get the details of patient appointment where appointment_date is after yesterday

SELECT *
FROM
    patient_appointment_details
WHERE
    DATE(appointment_date) > SUBDATE(CURRENT_DATE, 1);

Example3: MySQL where date greater than 7 days:

Get the details of patient appointment where appointment_date is after 7 days:

SELECT *
FROM
    patient_appointment_details
WHERE
    DATE(appointment_date) > ADDDATE(CURDATE(), 7);

Example 4: Check date greater than today date or not:

To do so, we'll look in the table to see which row has a value greater than today's date.

SELECT * FROM geeksforgeeks WHERE Deliver > GETDATE();

Example 5: sql for date greater than:

SELECT * FROM ATable WHERE DateField >= Convert(datetime, '2021-12-17 18:25:29')

In your SELECT statement with WHERE clause, we're establishing a condition on which MySQL returns the unique rows of the result set using the SQL Server DISTINCT clause to remove duplicates from more than one column. When we use the LIMIT clause in conjunction with the DISTINCT clause in MySQL queries, we're essentially giving the server a limit on the number of unique rows in the result set that will be returned.

In this scenario, the Separate keyword applies to each field listed after it, resulting in distinct combinations.

Example 1: We can use WHERE and LIMIT clause with DISTINCT as follows on the table named ‘testing’:

Select * from testing;

Output:

+------+---------+---------+
| id   | fname   | Lname   |
+------+---------+---------+
|  200 | Raman   | Kumar   |
|  201 | Sahil   | Bhalla  |
|  202 | Gaurav  | NULL    |
|  203 | Aarav   | NULL    |
|  204 | Harshit | Khurana |
|  205 | Rahul   | NULL    |
|  206 | Piyush  | Kohli   |
|  207 | Lovkesh | NULL    |
|  208 | Gaurav  | Kumar   |
|  209 | Raman  | Kumar    |
+------+---------+---------+

Select DISTINCT Lname from testing where Lname IS NOT NULL limit 3;

+---------+
| Lname   |
+---------+
| Kumar   |
| Bhalla  |
| Khurana |
+---------+

Example 2:

SELECT DISTINCT first_name, last_name
FROM employees
WHERE employee_id >=50
ORDER BY last_name;

This SQL Server DISTINCT clause example would return every unique first_name and last_name combination from the employees table with an employee_id higher than or equal to 50. The results are sorted by last_name in ascending order.

Example 3: SELECT/DISTINCT statement with WHERE clause to obtain just those unique employees whose paid pay is more than or equal to 4500. Look at the following query and result-set:

Query:

SELECT DISTINCT(emp_name) FROM sto_emp_salary_paid
 WHERE emp_sal_paid >= 4500;

A column with the value NULL indicates that it is empty. This is not the same as a value of 0 or false, or even an empty string.

To match a value to a NULL value, use the comparison operator equal to (=). Actually, it depends on your database management system and its settings.

Example 1: let’s look at what happens if I try to compare the DOB column to NULL.

SELECT * FROM Pets 
WHERE DOB = NULL;

Result:

Example 2: For example, the following statement will not return the correct result:

SELECT employee_id,
	first_name,
	last_name,
	phone_number
FROM
	employees
WHERE
	phone_number = NULL;

The SQL WHERE keyword is used to conditionally choose data by adding it to an existing SQL SELECT query. We can use the WHERE keyword to insert, update, and delete data from table(s), but we'll keep with conditionally obtaining information for now because we already know how to use the SELECT keyword.

When comparing a string value to another string value, use quotes around the string.

Example 1: For example, if we wanted to get information about all pets named Tom, we could do the following:

SELECT *
FROM Pets
WHERE PetName = 'Tom';

Result:

+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Tom       | 2020-11-20 |
| 6       | 3           | 4         | Tom       | 2020-09-17 |
+---------+-------------+-----------+-----------+------------+

Our query uses the equals operator (=) to compare the equality of the value of the PetName column and the string Tom.

In our case, we can see that the pet hotel currently has two pets called Tom.

Example 2: In order to illustrate better the WHERE keyword applications, we are going to add 2 columns to the Users table we used in the previous chapters and we'll also add a few more rows with actual data entries:

FirstName	LastName	DateOfBirth	Email				City
John		Smith		12/12/1969	john.smith@john-here.com	New York
David		Stonewall	01/03/1954	david@sql-tutorial.com		San Francisco
Susan		Grant		03/03/1970	susan.grant@sql-tutorial.com	Los Angeles
Paul		O'Neil		09/17/1982	paul.oneil@pauls-email.com	New York
Stephen		Grant		03/03/1974	sgrant@sgrantemail.com		Los Angeles

SQL query:

SELECT FirstName, LastName, City
FROM Users
WHERE City = 'Los Angeles'

Output:

FirstName	LastName	City
Susan		Grant		Los Angeles
Stephen		Grant		Los Angeles

Our SQL query used the "=" (Equal) operator in our WHERE criteria.

Example 3: Using WHERE clause with the equal (=) operator example:

The following statement uses the WHERE clause customers whose first names are Jamie:

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie';

The EXISTS command is used in the conditional clause to determine if a subquery produces a result or not. The WHERE EXISTS clause is used to restrict records from an outer query from appearing in an inner query.

Syntax:

SELECT col_name1
FROM `table1`
WHERE EXISTS (
    SELECT col_name2
    FROM `table2`
    WHERE col_name3 = 10
  )

In the example above, if there is at least one row in table2 whose col_name3 contains the value 10, then the subquery will return at least one result. From then on, the condition will be checked and the main query will return the results of col name_col1 of table1 .

Example 1: In order to show a concrete example of application, let's imagine a system composed of a table which contains orders and a table containing products.

Order table:

c_id	c_date_purchase	c_product_id	c_quantity_product
1	    2014-01-08	    2	    	    1
2	    2014-01-24	    3	        	2
3	    2014-02-14  	8	        	1
4	    2014-03-23  	10	        	1

Product table:

p_id	p_name		p_date_addition	p_price
2	Computer	2013-11-17	799.9
3	Keyboard	2013-11-27	49.9
4	Mouse		2013-12-04	15
5	Screen		2013-12-15	250

It is possible to run a SQL query that lists all orders for which a product exists. The following is how this request can be interpreted:

SELECT *
FROM command
WHERE EXISTS (
    SELECT *
    FROM product
    WHERE c_product_id = p_id
)

Result:

c_id	c_date_purchase	c_product_id	c_quantity_product
1   	2014-01-08  	2	        	1
2	    2014-01-24	    3	        	2

The result clearly shows that only orders n°1 and n°2 have a product that is in the product table (see the condition c_product_id = p_id). This query is interesting knowing that it does not influence the result of the main query, unlike the use of a join which will concatenate the columns of the 2 joined tables.

Example 2: That isn't to suggest that EXISTS is always the more efficient option. There are times when an LEFT JOIN WHERE IS NULL is more efficient than NOT EXISTS, but that's a topic for another article. Also, if there's a chance you'll require columns from the table you're restricting against in the future and it's a 1-1 relationship, I recommend joining the table. This makes it easier and eliminates the need to rewrite your query later.

With all that said, let’s look at an example of WHERE EXISTS:

SELECT COUNT(1)
FROM sales.SalesOrderDetail sod
WHERE EXISTS
(
SELECT 1
FROM production.Product
WHERE ProductID = sod.ProductID — first correlate inner and outer query
AND name LIKE ‘%mountain%’
)

Example 3:

Consider the Customer table with the following records:

ID	Name		Age	Address	Salary
1	Aarav		36	Udaipur	35000
2	Vivaan		33	Mumbai	30000
3	Reyansh		28	Chennai	40000
4	Muhammad	29	Udaipur	50000
5	Sai		27	Mumbai	27000

Consider the Order table with the following records:

OID	Date			CustomerID	Amount
101	2019-11-20 00:00:00	2		1800
102	2019-10-08 00:00:00	3		3000
103	2018-05-20 00:00:00	4		2500

SQL QUERY

SELECT Name, Address 
FROM Customer
WHERE EXISTS (SELECT * 
FROM Order 
WHERE Customer.ID = Order.CustomerID);

Output:

Name		Address
Vivaan		Mumbai
Reyansh		Chennai
Muhammad	Udaipur

The following code is an example, which would fetch the Name, Address from Customer table where Customer.ID = Order.CustomerID.

Example 4: Let's look at how to use EXISTS to pick where count larger than one for any column.

Get all the information on clients who bought online and at the store. For the answer, look at the question below.

SELECT 
    cd1.*
FROM
    customer_data cd1
WHERE 
    EXISTS( SELECT 
            *
        FROM
            customer_data cd2
        WHERE
                cd1.mode_of_shopping != cd2.mode_of_shopping
                AND cd1.customer_id = cd2.customer_id)
ORDER BY cd1.customer_id;

The key differences between the IN operator and the EXISTS operator are listed below in a tabular manner:

Example 1: Your issue is that floating point numbers are inherently inaccurate. As you've noticed, comparing what appears to be 60.5 to a literal 60.5 may not work.

A common technique is to calculate the difference between two values and consider them equal if it is less than some predetermined epsilon:

SELECT Name FROM Customers WHERE ABS(Weight-60.5) < 0.001

For better performance, you should actually use:

SELECT Name FROM Customers WHERE Weight BETWEEN 64.999 AND 65.001

Example 2:

Table name : prod_price

id price
----- --------
1 15.50
2 20.50
3 35.75

select * from prod_price where price = 35.75;

select the rows where the price is 35.75, but the select query returns the empty set.

GROUP BY is the command that is used to aggregate the output. Choose the startdate and then the aggregates you want to calculate. Otherwise, everything should be good.

The GROUP BY phrase divides the rows into groups (It is like a Pivot to grouping columns member to see aggregated value). The columns that you specify in the GROUP BY clause establish the groups.

Definitely. It will filter the records based on your date range and then aggregate them by each day where data is available.

Syntax:

SELECT
    select_list
FROM
    table_name
WHERE Condition
GROUP BY
    column_name1,
    column_name2, ..

Example 1: Below Query Product_Category is appear only For individual Product_Container:

SELECT
       Product_Category, Product_Container
FROM
       [dbo].[Customers_Tbl]
WHERE
      ([Product_Category] = 'Furniture')  
      OR 
      ([Product_Category] = 'Office supplies')
GROUP BY 
       Product_Category, Product_Container

Example 2: The WHERE clause goes before the GROUP BY:

select cu.CustomerID,cu.FirstName,cu.LastName, COUNT(si.InvoiceID)as inv 
from Customer as cu 
inner join SalesInvoice as si 
   on cu.CustomerID = si.CustomerID 
where cu.FirstName = 'mark' 
group by cu.CustomerID,cu.FirstName,cu.LastName

Example 3: this query will give you a count of employees for each startdate:

SELECT startdate, count(*)
FROM employees 
WHERE startdate >= '15-jan-2011' 
      AND startdate <= '20-aug-2011'
GROUP BY startdate

The major distinction is that the WHERE clause is used to filter records before any groupings are established, whereas the HAVING clause is used to filter values from a group. We shall first study these SQL clauses before comparing them.

You can use the HAVING Clause to define conditions that control which group results appear in the results.

The WHERE clause applies conditions to the columns selected, whereas the HAVING clause applies conditions to the groups formed by the GROUP BY clause.

1. In the logical order of query processing, the WHERE clause comes after the FROM clause, which implies it comes before the GROUP BY clause, whereas the HAVING clause comes after groups are established.

2. The WHERE clause in GROUP BY can relate to any column from a table, but only non-grouped or aggregated columns can be used.

3. If you use the HAVING clause without the group by clause, it can refer to any column, but unlike the WHERE clause, the index will not be applied. The next examples have the same result set, but "where" will utilise the id index and "having" will perform a table scan.

select * from table where id = 1
select * from the table having id = 1

4. With the HAVING clause, you can apply an aggregate function to filter rows. You can use an aggregate function in the logical expression since the HAVING clause is executed after the rows have been grouped. The following query, for example, will only show classes with more than 10 students :

SELECT Course, COUNT(Course) as NumOfStudent 
FROM Training 
GROUP BY Course 
HAVING COUNT(Course)> 10

5. Another significant distinction between the WHERE and HAVING clauses is that WHERE uses an index while HAVING does not; for instance, the following two queries yield similar results, but WHERE uses an index while HAVING does a table scan.

SELECT * FROM Course WHERE Id = 101;
SELECT * FROM Course HAVING Id = 102;

6. The WHERE clause evaluates for per row since it is assessed before groups are established. The HAVING clause, on the other hand, is assessed after groups are generated, therefore it analyzes per group.

LIKE is a SQL logical operator that allows you to compare values that are similar rather than exact.

Example 1: Let’s display the first name, last name, and city of each customer whose city name starts with the letter 'S'.

SELECT last_name, first_name, city 
FROM customer
WHERE city LIKE 'S%';

Output:

last_name	first_name	city
Lisa		Black		Singapore
Milan		Brick		Seoul
Laura		Williams	Seattle

The % wildcard was used to represent zero or more unknown characters. Anywhere in the string, this wildcard can be used.

Example 2: In this case, the Billboard Music Charts dataset will provide rows where "group" begins with "Snoop" and is followed by any number and combination of characters.

Run the code to see which results are returned.

SELECT *
 FROM tutorial.billboard_top_100_year_end
 WHERE "group" LIKE 'Snoop%'

Note: Because GROUP is the name of a SQL function, "group" appears in quotations above. The double quotes (rather than single: ') indicate that you are referring to the column name "group" rather than the SQL function. When you use double quotes around a word or phrase, it means you're referring to that column's name.

Example 3: Consider we've an employees table in our database with the following records:

+--------+------------------+------------+--------+---------+
| emp_id | emp_name         | hire_date  | salary | dept_id |
+--------+------------------+------------+--------+---------+
|      1 | Ethan Hunt       | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana     | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor     | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard     | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank     | 2008-06-24 |   5600 |    NULL |
|      6 | simons bistro    | 2009-04-01 |   6000 |       1 |
+--------+------------------+------------+--------+---------+

Now, let's say you want to find out all the employees whose name begins with S letter.

SELECT * FROM employees 
WHERE emp_name LIKE 'S%';

After executing the query, you'll get the output something like this:

+--------+------------------+------------+--------+---------+
| emp_id | emp_name         | hire_date  | salary | dept_id |
+--------+------------------+------------+--------+---------+
|      3 | Sarah Connor     | 2005-10-18 |   8000 |       5 |
|      6 | simons bistro    | 2009-04-01 |   6000 |       1 |
+--------+------------------+------------+--------+---------+

By default, nonbinary string comparisons (CHAR, VARCHAR, TEXT) in MySQL are case-insensitive, whereas binary string comparisons (BINARY, VARBINARY, BLOB) are case-sensitive.

In SQL, the LIKE operator is combined with the WHERE clause to search for specific patterns in a table column. There are two wild card operators in SQL that are used in conjunction with the LIKE operator to make searching more effective.

The SQL WILDCARD Operators are combined with the LIKE operator to improve table search performance.

Special characters and wildcards characters may have been used with the LIKE operator to match a pattern from a word.

You can use SQL wildcards to search data within a table.

SQL LIKE & Wildcard operators Types:

• % (percentage) operator
• _ (underscore) operator

SQL LIKE condition with _ (underscore) WILDCARD character to matches any exactly single character with in string.

[char_list] – Using this syntax, we can specify more than one character in ‘char_list’ to search and match in a given string.

Example 1: SQL LIKE condition with _ (underscore) WILDCARD character to matches any exactly single character with in string.

SELECT * FROM users_info WHERE name LIKE 'Pa_l S__gh';

Output:

NO  NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
4   Paul Singh            1343 Prospect St                000-444-7585

Example 2: Display only those employees whose name ends with the letter a.

SELECT *
FROM Employee
WHERE EmployeeName LIKE '%a';

Note: SELECT * is used here to select all columns in the data base table at the same time. It saves time compared to listing each column individually.

Example 3:

SELECT *
FROM agents
WHERE agent_name  NOT LIKE 'M%';

The IN keyword enables you to choose values from a list of values that match any of them. The list can be a set of constants or, more typically, a subquery, with the expression being a constant or a column name.

It returns all of the rows that match. You can save time and minimise the size of your query by using an IN list.

The IN keyword must be divided by commas and contained within parentheses. Around character, date, and time values, use single quotes.

Example 1:

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

When you use IN, though, you obtain the same results. Following the IN keyword, the items must be separated by commas and contained in parenthesis. Single quotes should be used around character, date, and time values.

Example 2: How to give ‘IN’ (LIST) Condition in SQL WHERE clause.

SELECT *
FROM EMPLOYEE
WHERE MY_NAME IN ('SRINIMF', 'MOHNA', 'TORAD')

Example 3:

SELECT CompanyName, State
   FROM Customers
   WHERE State IN( 'ON', 'MB', 'PQ');

Syntax for Writing a query using IN :

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

Instead of Writing a query using OR :

SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

Syntax for where id is in list sql:

select * from table where id in (id1,id2.........long list)

Example 1: SQL - where Id in (list of Ids):

--SQL EXECUTION QUERY --

declare @Ids varchar(100) set @Ids='7,13,120'

select * from table where Id in (@Ids)

This results me "Conversion failed when converting the varchar value '7,13,120' to data type int."

I want to implement "WHERE IN" query on integer type but my values will be stored in a local variable which can be of any type int or varchar.

The Boolean logical operators are used in multiple SQL Where Clause Conditions within the WHERE clause: AND, OR and NOT, Like >, >=, <, <=.

It is good practice to use parenthesis if using multiple Boolean operators to avoid confusion.

1. ()
2. AND
3. NOT
4. OR

Example 1:

SELECT *
FROM EMPLOYEE
WHERE (MY_SALARY='90000' or MY_BONUS IS NULL)
AND MY_NAME LIKE %SRI%

When you run the above query it first evaluates ‘()’, then AND, then OR.

Example 2:

Lets take the same table:

+------+----------+---------+----------+
|SSN   | EMP_NAME | EMP_AGE |EMP_SALARY|
+------+----------+---------+----------+
|  101 | Steve    |  23     |  9000.00 |
|  223 | Peter    |  24     |  2550.00 |
|  388 | Shubham  |  19     |  2444.00 |
|  499 | Chaitanya|  29     |  6588.00 |
|  589 | Apoorv   |  21     |  1400.00 |
|  689 | Rajat    |  24     |  8900.00 |
|  700 | Ajeet    |  20     | 18300.00 |
+------+----------+---------+----------+

Let's get the details of employees who are over the age of 23 and earn more than $5,000. We must utilize many conditions in the where clause for such a query.

Query:

SELECT * 
FROM EMPLOYEES
WHERE EMP_SALARY > 5000 AND EMP_AGE > 23;

Result:

+------+----------+---------+----------+
|SSN   | EMP_NAME | EMP_AGE |EMP_SALARY|
+------+----------+---------+----------+
|  499 | Chaitanya|  29     |  6588.00 |
|  689 | Rajat    |  24     |  8900.00 |
+------+----------+---------+----------+

Example 3:

USE AdventureWorks

To display all the records whose city is ‘Bothell’ and id is greater then

SELECT * FROM Person.Address WHERE City=‘Bothell’ AND AddressID > 50

To display all the records whose city is ‘Bothell’ or ‘Seatlle’

SELECT * FROM Person.Address WHERE City=‘Bothell’ OR City=‘Seattle’

Check if column is not null

You should check whether a column is empty or not before dropping it from a table or changing its values.

Example 1: Lets take the same example that we have seen above. In this example, we will check for the not null values.

Table: Employees

EmployeeName   EmployeeAge  EmployeePhoneNo  EmployeeAddress
------------   -----------  ---------------  ---------------
Cindi           34           95XXXXXXX8        Noida
Linda           35                             Agra
Shaun           33           75XXXXXXX4        Delhi
Timmy           34                             Noida
Pappu           36                             Noida

The EmployeeName and EmployeeAddress data of employees whose EmployeePhoneNo field is not null will be retrieved using the SQL statement below.

SELECT EmployeeName, EmployeeAddress
FROM Employees
WHERE EmployeePhoneNo IS NOT NULL;

Result:

EmployeeName   EmployeeAddress
------------   ---------------
Cindi           Noida
Shaun           Delhi

Example 2:

select count(*)
from Certifications
where price is not null;

MySQL The NOT EQUAL TO (>) operator is used to return a set of rows (from a table) after ensuring that the two expressions on either side of the NOT EQUAL TO (<>) operator are not equal. These operators can also be found in WHILE loops, IF statements, HAVING clauses, join predicates, SQL GROUP BY, and CASE statements in T-SQL programming.

In the WHERE clause, avoid employing the not equal operator (<>,!=).

As it performs table and index scans, the rule looks for the not equal operator in the WHERE clause.

Example 1: Consider replacing the not equal operator with equals (=) or inequality operators (>,>=,<,<=) if possible.

SELECT * 
FROM  Table1 t1
WHERE t1.value <> 'value1';

-- Equal operatror used in the WHERE clause.
SELECT * 
FROM Table1 t1
WHERE t1.computed_value = 0

Example 2: MySQL not equal to (<>) operator:

The following MySQL statement will retrieve entries from the table publisher that contain publishers that do not reside in the United States of America.

SELECT pub_name,country,pub_city,estd 
FROM publisher 
WHERE country <>"USA";

Example 3: Consider this SQL query.

SELECT OrderID     
      ,SalespersonPersonID
      ,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2;

This SQL query returns all rows from the Sales.Orders table where the SalespersonPersonID is not 2.

Because the underlying subquery returns TRUE and Not exists returns false, the NOT EXISTS operator returns true if the underlying subquery returns Empty records. The NOT EXISTS operator will return false if the inner subquery matches a single record, and the subquery execution can be halted.

The NOT EXISTS Syntax

SELECT column1, column2, column3 FROM table_name
WHERE NOT EXISTS
(SELECT column_name FROM table_name );

Example 1: Let's make the condition Sales > 10,000, which is a bogus condition. As a result, the SQL NOT EXISTS operator returns all of the records.

-- SQL Server NOT EXISTS Example
USE [SQL Tutorial]
GO
SELECT Employ1.[EmpID]
      ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name]
      ,Employ1.[Education]
      ,Employ1.[Occupation]
      ,Employ1.[YearlyIncome]
      ,Employ1.[Sales]
      ,Employ1.[HireDate]
  FROM [Employee] AS Employ1
  WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 
		    WHERE Employ1.[EmpID] = Employ2.[EmpID] 
			 AND [Sales] > 10000
  )

Example 2: We may use the following SQL statement with the WHERE clause to find all student records that have no related student grade with a value less than 9:

SELECT
    id, first_name, last_name
FROM
    student
WHERE NOT EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

Example 3: Assuming these tables should be joined on employeeID, use the following:

SELECT  *
FROM employees e
WHERE NOT EXISTS
        (
        SELECT  null 
        FROM    eotm_dyn d
        WHERE   d.employeeID = e.id
        )

SQL WHERE NOT IN SQL Where clauses can be used in any sequence and in any combination, depending on the situation. It will work with any kind of data.

The SQL Server NOT IN operator is used to substitute a group of parameters that are joined with an AND using the > (or!=) operator. For SELECT, UPDATE, or DELETE SQL operations, it can make code easier to read and understand. In most cases, it will have no effect on performance.

The SQL NOT IN operator might be a better option. The NOT IN operator compares a single item, often a column, to a comma-separated list of possible match values contained in parentheses. The value to the left of the NOT IN operator is then compared to the complete list, one by one, and an exact match to any one member of the list will result in the argument evaluating as false.

Syntax:

SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 NOT IN (value1, value2, etc);

Example 1: USE IN & NOT IN OPERATORS IN A WHERE CLAUSE IN SELECT QUERIES:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age in (25, 29) AND City NOT IN (‘Chennai’, ‘Delhi’);

Example 2: List all products that are not priced at $10, $20, $30, $40, or $50.

SELECT Id, ProductName, UnitPrice
  FROM Product
 WHERE UnitPrice NOT IN (10,20,30,40,50)

Example 3: The following SQL statement converts the 3 "LastEditedBy <>" arguments using a single NOT IN operator instead.

SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy NOT IN (11,17,13);

This query is functionally equivalent to the one above, but is shorter and easier to read.

Example 4: The following statement returns values where empno not having 101, 103, 105, 107, 109:

SELECT *
 FROM emp     
 WHERE empno NOT IN (101, 103, 105, 107, 109);

Any row that does not match the search pattern is returned when the LIKE operator is paired with the NOT operator.

A column of type varchar is used with the NOT LIKE operator in SQL. It is most commonly used with the percent symbol, which can indicate any string value, including the null character 0.

This operator doesn't care about case in the string we provide it.

This operator is commonly used to eliminate rows where a string begins or ends with a specific string.

Example 1: To see a book whose name does not begin with “post”, we can run the following command:

SELECT *
FROM
   Book
WHERE
  name NOT LIKE 'Post%';

Example 2: MySQL NOT LIKE operator with (%) percent with WHERE clause:

The following MySQL statement removes those rows from the table author that have the character 'W' as the first character in their aut_name.

SELECT aut_name, country
FROM author 
WHERE aut_name NOT LIKE 'W%';

Example 3: The following query returns the rows in which FirstName do not start with A:

SELECT * FROM Student
WHERE FirstName NOT LIKE 'A%'

The SQL server presents records in an indiscriminate order when you run a SELECT query without any sorting choices. The SQL server, in most situations, returns records in the same order as they were inserted to the database.

We've already demonstrated how to filter records using the WHERE clause with SELECT, UPDATE, and DELETE statements. With or without the ORDER BY declaration, the WHERE clause can be used. Filtering records can be done using finite values, comparison values, or sub-SELECT queries. When filtering data, the WHERE clause provides numerous alternatives.

The order by and where with a condition to demonstrate a range.

Example 1: The LoginID column from the HumanResources table is selected in the example below. Employee table, VacationHours column equals 8, and data is ordered in ascending order by HireDate, which is inferred.

USE AdventureWorks;
GO 
SELECT LoginID
FROM HumanResources.Employee
WHERE VacationHours = 8
ORDER BY HireDate;
GO

Example 2: The equal (=) sign has been used in various contexts. You can also use parallels. For example, you might require a list of customers with IDs ranging from 300 to 400. These values are displayed in the SQL statement below.

SELECT * FROM Customer
WHERE CustomerId >=200 AND CustomerId <= 300
ORDER BY State 

Notice how the terms >= and = are utilised. The values to the right of the equal sign are included. In other words, the search includes the numbers 200 and 300. Because there are no 200 or 300 in this Customer table, such numbers are not returned. The SQL statement also includes a "AND" in the syntax. In this scenario, the AND keyword incorporates a filter from the next SQL line, "Customer = 300." The AND keyword instructs the SQL statement to filter records based on both inputs.

Example 3:

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

The SQL REGEXP LIKE method is similar to the LIKE condition, except it matches regular expression patterns to execute like condition. If the string matches the regular expression, then this function returns true. Instead of using simple wildcard character matching, it employs regular expressions. This condition analyzes strings using the input character set's characters.

Example 1: The following query retrieves the first and last names of employees with the first names Steven or Stephen (where first_name starts with Ste and finishes with en, with v or ph in between):

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

Output:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

Example 2: Example for regular expression:

SELECT v FROM t WHERE REGEXP_LIKE(v,'a');

Output:

  v   
------
 Aaa
 aaa
 abc
 abc1

Example 3: Consider following example is REGEXP_LIKE function fetching 'Opa?l' regular expression pattern from the name.

SELECT employee_name
 FROM emp_info
 WHERE REGEXP_LIKE (name, 'Opa?l', 'im');

Output:

employee_name
-------------
         Opal
          Opl

Example 1: write a SQL query to find those rows where col1 does not contain the string ( _/ ). Return col1.

SELECT *
FROM testtable
WHERE col1 NOT LIKE '%/_//%' ESCAPE '/';

Output:

col1
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44

Example 2: sql column contains special character with WHERE clause:

SELECT Col1
FROM TABLE
WHERE Col1 like '%[^a-Z0-9]%'

Example 3: Lines that are LIKE 'TEST %' will include all lines that are LIKE '\_TEST% %' ESCAPE ' \'. If all lines which that interest you conform to the mask, they will also appear when you perform the first. You can have records that correspond to the first but not the second, although:

create table testdata until

SELECT 'ABCTESTXYZ' val double UNION ALL

SELECT "AB_TESTXYZ" FROM double

Table created.

SELECT * FROM testdata WHERE val LIKE '\_TEST% %' ESCAPE ' \'

Output:

VAL
----------
AB_TESTXYZ

Subqueries can be used to qualify a column against a collection of rows in a DML statement by using them to help establish conditions in the WHERE clause.

Subqueries allow you to integrate data from multiple tables into a single result. These are also known as nested inquiries. Subqueries, as the name suggests, include one or more queries, one inside the other.

Subqueries are quite flexible, which can make them a little difficult to grasp. In most circumstances, utilize them wherever an expression or table specification is possible.

In the SELECT, FROM, WHERE, and HAVING clauses, you can employ subqueries. A single value or several rows can be returned by a subquery.

Example 1: query as a subquery in the WHERE clause of a query as follows:

SELECT film_id,
	title
FROM
	film
WHERE
	film_id IN (
SELECT
	inventory.film_id
FROM
	rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
	WHERE
	return_date BETWEEN '2005-05-29'
	AND '2005-05-30'
);

Example 2: Run the subquery to get the list of territories that had year to date sales less than 5,000,000:

SELECT TerritoryID
FROM   Sales.SalesTerritory
WHERE  SalesYTD < 5000000

Example 3: You can use comparison operators to compare the value of a column to the value returned by a subquery, for example. In the following example, I compare the BusinessEntityID value in the Person database to the value returned by a subquery using the equal (=) operator:

SELECT BusinessEntityID,
  FirstName,
  LastName
FROM
  Person.Person
WHERE
  BusinessEntityID =
  (
    SELECT BusinessEntityID
    FROM HumanResources.Employee
    WHERE NationalIDNumber = '895209680'
  );

Example 4: The third-floor department numbers can be found using the following subquery. The outer query gets the names of employees on the third floor.

SELECT ename
FROM employee
WHERE dept IN
       (SELECT dno
       FROM dept
       WHERE floor = 3);