The SQL ANY Operator
The ANY operator:
returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the values in the range.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
-----------
The SQL ALL Operator
The ALL operator:
returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
create table student( sname varchar(20), sage int);
create table faculty( fname varchar(20), fage int);
insert into faculty values("Venkat",60),("ramesh",33)
,("lakshman",38) ,("praveen",40),("sreenu",42)
,("naveen",39);
insert into student values("salman",20),("tinku",33)
,("karthik",50) ,("rajesh",28),("mallik",22)
,("ravi",41);
---------
select sname from student where sage > any
(select fage from faculty);
insert into student values("salman1",62),("tinku1",62);
----------------------
Warning: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe: ignoring option
'--no-beep' due to invalid value ''
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.51-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use vlrinst;
Database changed
mysql> create table student( sname varchar(20), sage int);
Query OK, 0 rows affected (0.24 sec)
mysql> create table faculty( fname varchar(20), fage int);
Query OK, 0 rows affected (0.48 sec)
mysql> insert into faculty values("Venkat",30),("ramesh",33)
-> ,("lakshman",38) ,("praveen",40),("sreenu",42)
-> ,("naveen",39);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from faculty;
+----------+------+
| fname | fage |
+----------+------+
| Venkat | 30 |
| ramesh | 33 |
| lakshman | 38 |
| praveen | 40 |
| sreenu | 42 |
| naveen | 39 |
+----------+------+
6 rows in set (0.00 sec)
mysql> insert into student values("salman",20),("tinku",33)
-> ,("karthik",50) ,("rajesh",28),("mallik",22)
-> ,("ravi",41);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from student;
+---------+------+
| sname | sage |
+---------+------+
| salman | 20 |
| tinku | 33 |
| karthik | 50 |
| rajesh | 28 |
| mallik | 22 |
| ravi | 41 |
+---------+------+
6 rows in set (0.00 sec)
mysql> select sname from student where sage > any
-> (select fage from faculty);
+---------+
| sname |
+---------+
| tinku |
| karthik |
| ravi |
+---------+
3 rows in set (0.00 sec)
mysql> select sname from student where sage > all
-> (select fage from faculty);
+---------+
| sname |
+---------+
| karthik |
+---------+
1 row in set (0.00 sec)
mysql> select sname from student where sage >
-> all (select fage from faculty);
+---------+
| sname |
+---------+
| karthik |
+---------+
1 row in set (0.00 sec)
mysql> select sname from student where sage >
-> any (select fage from faculty);
+---------+
| sname |
+---------+
| tinku |
| karthik |
| ravi |
+---------+
3 rows in set (0.00 sec)
mysql> insert into faculty values("Venkat",60);
Query OK, 1 row affected (0.15 sec)
mysql> select sname from student where sage >
-> all (select fage from faculty);
Empty set (0.00 sec)
mysql> insert into student values("salman1",62),("tinku1",62);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select sname from student where sage >
-> any (select fage from faculty);
+---------+
| sname |
+---------+
| tinku |
| karthik |
| ravi |
| salman1 |
| tinku1 |
+---------+
5 rows in set (0.00 sec)
mysql> select sname from student where sage >
-> all (select fage from faculty);
+---------+
| sname |
+---------+
| salman1 |
| tinku1 |
+---------+
2 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.