IN, ALL, ANY operators in SQL

-

Hello, and welcome to the blog. In this blog we are going to talk about some operators in SQL which help us when we are are adding WHERE clause with large data.

This is going to be a good practice when we are going to learn about "SUBQUERIES". This is going to be a very small but very useful blog.

So, In this blog we are going to talk about
  1. IN Operator
  2. BETWEEN Operator
  3. ALL Operator
  4. ANY Operator
Currently our Employee1 table look like this:


So, let's get started,

1) IN Operator

  1. The IN operator allows you to specify multiple values in a WHERE clause.
  2. The IN operator is shorthand for multiple "OR" conditions.
  3. let's jump to the syntax:
    SELECT column_name
    FROM table
    WHERE coulmn_name IN (value_1,value_2,...,value_n)
  4. You can always replace values in syntax with a Subquery.
  5. Let's see a example:
    Suppose we have to find Employees of IT and Sales department
  6. Solution with IN operator:
    SELECT FirstName
    FROM Employee1
    WHERE DepartmentId IN (2, 4)

  7. Solution without IN operator:
    SELECT FirstName
    FROM Employee1
    WHERE DepartmentId =1 OR DepartmentId=2

  8. We got the same result it is just that we saved a couple of seconds of writing query with IN operator. This couple of seconds turns to couple of minutes when we will advance our skills.

2) BETWEEN Operator

  1. As the name suggests BETWEEN operator selects records with value within a given range.
  2. The values can be numbers, text or dates.
  3. let's see the syntax:
    SELECT column_name
    FROM table
    WHERE coulmn_name BETWEEN value_1 AND value_2
  4. Let's see a example:
    Suppose we have to find Employees of Ranging from 30,000 to 50,000
  5. Solution :
    SELECT FirstName, Salary
    FROM Employee1
    WHERE Salary BETWEEN 30000 AND 50000

  6. Now I want to make one more point here : BETWEEN operator is inclusive that means it will also check for values which are there in query.
  7. You can see in the result we also have employees with salary as 50000 in our result set.

3) ALL Operator

  1. ALL operator provides you "AND" functionality with conditional operators ('<', '>', '=', '<=', '>=', '!=')
  2. When you use ALL operator all values must satisfy the condition.
  3. This operator can only be used with "Subquery". We will learn Subquery in upcoming blogs.
  4. let's see the syntax:
    SELECT column_name
    FROM table
    WHERE coulmn_name conditional_operator ALL (subquery)
  5. Example:
    SELECT FirstName
FROM Employee1
WHERE DepartmentId = ALL (
		SELECT Id
		FROM Department
		WHERE DepartmentName IN (
				'IT'
				,'Sales'
				)
		)

  6. As Id field can never hold multiple values we got nothing in result set.

4)ANY Operator

  1. ANY is exactly same as ALL operator in terms of rules and syntax, but its different in functionality.
  2. The ANY operator returns TRUE if any of the subquery value meet the condition
  3. Let's understand it better by using the same query which we have used in ALL. We just have to replace ALL with ANY.
  4. Example:
    SELECT FirstName
FROM Employee1
WHERE DepartmentId = ANY (
		SELECT Id
		FROM Department
		WHERE DepartmentName IN (
				'IT'
				,'Sales'
				)
		) 
Congratulations learning some more topics in SQL. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

Comments

Post a Comment

Popular posts from this blog

SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
Read more

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more

More clauses in SQL

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
Read more