Sub Query & It's operators ( IN , Between, =, ..) | SQL-MySQL | Belayet Hossain
Belayet Hossain ??
Data Analyst @ZnZ ? Power BI, SQL, Excel, Python (ETL), MySQL, Oracle, DBeaver ? Find insight & Making Decision ? Ex-Head of Quality Dept & 09 Y With smartphone Manufacturing & Service ? Ex- RFL, VIVO, Symphony ? EEE
Sub Query and it's operators (IN, BETWEEN, =,<,>, <=, >=)
?Sub Query:
A subquery in SQL is a query nested inside another query. It is used to perform operations that depend on the result of another query. Subqueries can appear in various clauses such as SELECT, FROM, WHERE, or HAVING.
?? Types of Subqueries:
?? Subqueries are used with operators such as =, <, >, >=, <=, IN, BETWEEN, EXISTS, etc..
→ Example:
We have two different table.
Table-1 name: ‘Student
Columns: student_id, Student_name, department.
Table-2 Name: ‘student_result’
Columns: student_id, Result .
Now, We have to find the student name, id, department for the 2nd height CGPA using above two tables.
-- TO find the 2nd height Result in the Student_result Table
select student_id, result
from student_result
order by result desc
limit 2,1
-- using sub query to find the student Name, id & department from the 'student' table
-- on the basis of the 2nd heights result's 'student_id' in the 'student result' table.
?Query:
select student_id, student_name, department
from student
where student_id =
(select student_id
from student_result
order by result desc
limit 2,1)
?
?
?? IN & NOT IN Operators
>> IN : Used when the subquery returns multiple values to check if a column's value matches any of them.
Syntax:
SELCT column1, column2, column3
FROM table_name
WHERE column_name IN (value1, value2,….)
?
Or,
SELCT column1, column2, column3
FROM table_name
WHERE column_name IN (SELECT column1 FROM table_name WHERE filter_condition)
?
→ Example 1: Find the information of them whose student_id are 1, 3 and 4 from bellow table ‘student’
领英推荐
Query:
select *
from student
where student_id in (1, 3, 4)
?
→ Example 2:
Find the id, name and department of those students whose admission date is 1st January 2022.
Using bellow 2 tables. ‘student’ and ‘student_admission_info’
?
Query:
select student_id
from student_admission_info
where admission_date in ('2022-01-01')
?
?
select student_id, student_name, department
from student
where student_id in
????????????????????????????????? (
????????????????????????????????? select student_id
????????????????????????????????? from student_admission_info
????????????????????????????????? where admission_date in ('2022-01-01')
????????????????????????????????? )
?Output:
>> NOT IN: The NOT IN operator is used to filter out rows where a value in a column does not match any value returned by a subquery or list. It effectively works as the opposite of the IN operator.
To implement NOT IN operator just use NOT IN in the query instead of IN.
?
?? BETWEEN & NOT BETWEEN:
>> BETWEEN: Used to check if a column's value lies within a range of values returned by a subquery.
Syntax:
SELCT column1, column2, column3
FROM table_name
WHERE column_name BETWEEN value1 and value2;
?
Example: Find the id & name of job solders whose salary 40k to 60k.
?
Query:
select employee_id , name
from employee
where salary between 40000 and 60000;
?
>> NOT BETWEEN: The NOT BETWEEN operator is used to filter rows where a column's value does not fall within a specified range (inclusive). It is the opposite of the BETWEEN operator.
To implement NOT BETWEEN operator just use NOT BETWEEN in the query instead of BETWEEN.
#dataanalytics #dataanlyst #SQL #mysql #powerbi #SubQuery #in #between