Where Clause

Introduction

Consider a scenario where you have massive data of the programmers; all you have to do is find the programmers who are comfortable with the Go language. It would be very time-consuming to seek each programmer's skill set that matches your requirements.

What if you had a single command to filter out those persons with the specific condition?

Thankfully, we have WHERE Clause, which is precisely utilized to filter out the sub-content from the entire content based on the supplied condition.

In this article, we will be analyzing the usage of the WHERE Clause in various use cases with different examples.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Let us now get started:

The SQL WHERE Clause is used to retrieve a sub-table from an entire table based on the condition specified. In layman's words, it returns the rows you are interested in.

Consider a gathering of boys and girls from which you have to extract the girls. You may manually notify the girls to come out in front of you to accomplish this.

However, in databases, the WHERE Clause restricted the record means those records are unmatched in where clause condition will be eliminated from the table.

Furthermore, a WHERE condition returns either true or false. When we use the WHERE clause to establish a condition, the query only executes for those records for which the condition specified by the WHERE clause is true.

Syntax:

Where [condition] -- returns either true or false

This Clause is mainly used with SELECT, UPDATE, and DELETE queries. Let us have a look at each query with where Clause one by one:

The table we are referring to analyze the Where clause is given below:

With SELECT Statement

The SELECT statement is used when we want to retrieve the data. To retrieve the needed data from the entire table, we could use the where clause with the SELECT statement.

The basic syntax of the SELECT statement with WHERE clause is as follows:

SELECT col_name/col_names from table_name
WHERE [condition]

Scenario-1: Assume you've been given an Employee table with some information about the employees. You need to retrieve the Employee whose emp_id is 2.

Query:

SELECT * from Employee
WHERE emp_id = 2;

Output:

In the preceding example, we applied a condition to an integer value field; but, what if we wanted to apply the condition to a string field? In that instance, the value must be enclosed in a single quote' '. Some databases accept double quotes, but all accept single quotes.

Let us have a look at such example:

Scenario-2: Your task is to find an individual familiar with the Go programming language.

Query:

SELECT * from Employee
WHERE emp_skillsSet = 'Go'; --condition

Output:

With UPDATE statement

As the name suggests, the Update statement is used to update the data in the table.

The basic syntax of the UPDATE statement with WHERE Clause is as follows:

UPDATE table_name set column_name = new_data
WHERE [condition];

Scenario 1: Let's say you want to update the name of an employee whose id is 1 to John. To do so, we must set the condition after the where Clause.

Query:

UPDATE Employee set emp_name = 'John'
WHERE emp_id = 1;

To see the updated data, we must specify the SELECT statement:

SELECT * from Employee;

Output:

Scenario 2: Update the emp_name to Ninja and emp_skillsSet to C++ whose id = 3.

Here we need to update two records of an employee. To do so, we must separate the updated records using comma(,).

Query:

UPDATE Employee set emp_name = 'Ninja' , emp_skillsSet = 'C++'
WHERE emp_id = 3;

SELECT * from Employee;

Output:

With DELETE statement

Yet again, the where Clause is used to delete any particular record from the table. Delete statement removes the existing records in a table.

Be careful when deleting records in a table. When you omit the WHERE clause, all records in the table will be deleted.

The basic syntax of the DELETE statement with the WHERE clause is as follows:

DELETE from table_name
Where [condition];

Scenario: Delete the record of an employee whose name is John.

Query:

DELETE from Employee
WHERE emp_name = 'John';

To display the updated records, specify the SELECT statement:

SELECT * from Employee;

Output:

Let us now have a look at some basic operators that can be used with the WHERE clause:

Must Read SQL Clauses

Frequently asked questions

Can we use two WHERE clauses in SQL?

However, if we try to utilize more than one where Clause, the code becomes less understandable and ugly. To make it more aesthetically pleasing, we can define numerous conditions in a single WHERE clause, such as getting rows based on the values in different columns. The AND and OR operators can combine two or more conditions into a compound condition.

What is the difference between the HAVING Clause and WHERE Clause in SQL?

WHERE Clause filters records from a table based on the condition supplied; in contrast, the HAVING Clause filters records from groups based on the condition provided.

Is the Where clause faster than the Having Clause?

According to SQL Standard, WHERE restricts the result set before returning records, whereas HAVING determines the result after bringing all the records. So, WHERE is the quickest.

Key takeaways

To summarize the discussion, we've discussed the usage of the Where Clause with DDL commands and understood each syntax with an appropriate example. We've also discussed the operators which can be utilized to make the best use of the Where Clause. It's your turn now to play with the data and do some experimenting.

Furthermore, if you want to grab a great insight into the SQL series, follow up on such articles and enhance your learning. Also, you can check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

WHERE Clause in SQL

Introduction

WHERE Clause

With SELECT Statement

With UPDATE statement

With DELETE statement

Frequently asked questions

Key takeaways