MySQL ANY ALL IN

mysql> select  4 >any(select 33  as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 0 |

+---+

1 row in set (0.00 sec)

mysql> select  34 >any(select 33  as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 1 |

+---+

1 row in set (0.01 sec)

-----------

ALL

mysql> select  34 >ALL(select 33  as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.4/en/any-in-some-subqueries.html

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=

MySQL exists

 select  exists  (select  'A' where 5>10) as R;

+---+

| R |

+---+

| 0 |

+---+

Because     the evaluated query return   empty set, if return any value doesn't matter the value   it will return  1, doesn't matter if is null or 0.

select  'A' where 5>10;

Empty set (0.00 sec)

------------------------    exists   here will return 1

select  exists  (select  NULL) as R;

+------+

| R    |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

-

 select  exists  (select  0) as R;

+---+

| R |

+---+

| 1 |

+---+

select 'Ambiorix' where exists(select 1);

+----------+

| Ambiorix |

+----------+

| Ambiorix |

+----------+

MySQL IF

mysql> select if(10>8,'A','B') as result;

+--------+

| result |

+--------+

| A      |

+--------+

--------------

mysql> select if(10>15,'A','B') as result;

+--------+

| result |

+--------+

| B      |

+--------+

MySQL case

mysql> set @i=4;

 mysql> select case @i

    -> when @i>3 then'A'

    -> else 'B'

    -> end as t;

------------

mysql> select case

    -> when 5>4 then  'A'

    -> when 5<6 then 'B'

    -> else 'C'

    -> end as t;

+---+

select upper(case when 3> 2 then 'h' when 5>1 then 'e' else 'r' end) as Letter;

+--------+

| Letter |

+--------+

| H      |

+--------+

Functions and Operators

  Functions and Operators

https://dev.mysql.com/doc/refman/8.4/en/functions.html

Data type

https://dev.mysql.com/doc/refman/8.4/en/data-types.html

books

 https://github.com/manjunath5496/MySQL-Books/blob/master/README.md

join

 CREATE TABLE members (

    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the tables members and committees :

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
mysql> SELECT * FROM members;
	+-----------+--------+
	| member_id | name   |
	+-----------+--------+
	|         1 | John   |
	|         2 | Jane   |
	|         3 | Mary   |
	|         4 | David  |
	|         5 | Amelia |
	+-----------+--------+
	5 rows in set (0.00 sec)

	mysql> SELECT * FROM committees;
	+--------------+--------+
	| committee_id | name   |
	+--------------+--------+
	|            1 | John   |
	|            2 | Mary   |
	|            3 | Amelia |
	|            4 | Joe    |
	+--------------+--------+
	4 rows in set (0.00 sec)



	mysql> select m.member_id,c.committee_id,m.name,c.name from members m  inner join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         3 |            2 | Mary   | Mary   |
	|         5 |            3 | Amelia | Amelia |
	+-----------+--------------+--------+--------+
	3 rows in set (0.01 sec)

	mysql> select m.member_id,c.committee_id,m.name,c.name from members m left  join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         2 |         NULL | Jane   | NULL   |
	|         3 |            2 | Mary   | Mary   |
	|         4 |         NULL | David  | NULL   |
	|         5 |            3 | Amelia | Amelia |
	+-----------+--------------+--------+--------+
	5 rows in set (0.00 sec)

	mysql> select m.member_id,c.committee_id,m.name,c.name from members m right  join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         3 |            2 | Mary   | Mary   |
	|         5 |            3 | Amelia | Amelia |
	|      NULL |            4 | NULL   | Joe    |
	+-----------+--------------+--------+--------+
	4 rows in set (0.00 sec)


	mysql> SELECT * FROM members where name not in(SELECT name FROM committees);
	+-----------+-------+
	| member_id | name  |
	+-----------+-------+
	|         2 | Jane  |
	|         4 | David |
	+-----------+-------+
	2 rows in set (0.01 sec)


	mysql> select * from committees where name not in ( select name from members);
	+--------------+------+
	| committee_id | name |
	+--------------+------+
	|            4 | Joe  |
	+--------------+------+
	1 row in set (0.00 sec)



Code language: SQL (Structured Query Language) (sql)