Introduction

MySQL Clauses are a set of rules that enhances the queries over the database. In this article, we learn a few of these MySQL Clauses. These clauses are also referred to as Nested queries.

Nested queries

A nested query is a collection of inner or subqueries. The subqueries or inner queries will execute first, and the result will be available to the outer query. There are many nested queries, out of which IN, OR are a few.

Important MySQL Clauses

MySQL WHERE

The WHERE clause in MySQL is used to filter the results of SELECT, INSERT, UPDATE, and DELETE statements. It indicates a specified location where the operation is to be performed.

• The WHERE clause can be used with one or more tables separated by a comma to contain various conditions, however, it is an optional part of the SELECT statement.
• Using the AND and OR operators, you can specify several conditions.
• A WHERE clause can also be used to specify a condition when using the DELETE or UPDATE SQL commands.
• In every programming language, the WHERE clause functions similarly to an if condition. This clause is used to compare a given value to the value of a field in a MySQL table. It returns that row if the given value from outside is equal to the available field value in the MySQL table.

Syntax:

[WHERE condition1 is true] SELECT field1, field2,...fieldN table name1, table name2... condition2..... [AND [OR]] condition1.....

Example:

SELECT *  
FROM officers  
WHERE address = 'Lucknow'  
AND officer_id < 5;

Output

MySQL DISTINCT

The DISTINCT clause in MySQL is used to remove duplicate records from a table and only retrieve the unique ones. Only the SELECT query uses the DISTINCT clause.

Syntax:

SELECT DISTINCT expressions  
FROM tables  
[WHERE conditions];

Example:

SELECT DISTINCT address  
FROM officers;

Output:

MySQL FROM

The FROM Clause in MySQL is used to pick records from a table. Using the JOIN condition may also fetch records from many tables.

Syntax:

FROM table1  INNER JOIN table2  
ON [table1.column1 = table2.column1 ] ;

Example:

SELECT *  
FROM officers  
WHERE officer_id <= 3;

Output:

MySQL ORDER BY

To sort the records in ascending or descending order, use the MYSQL ORDER BY Clause.

Syntax:

SELECT expressions  
FROM tables  
[WHERE conditions]  
ORDER BY expression [ ASC | DESC ];

Example:

SELECT *  
FROM officers  
WHERE address = 'Lucknow'  
ORDER BY officer_name;

Output:

MySQL GROUP BY

The GROUP BY clause in MYSQL is used to collect data from several records and group it by one or more columns. It’s usually found in SELECT statements.

On the grouped column, you can use aggregate functions like COUNT, SUM, MIN, MAX, AVG, and so on.

Syntax:

SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;

Example and Output

MySQL HAVING

MySQL IS AVAILABLE The clause is used in conjunction with the GROUP BY clause. It always returns the rows with the TRUE condition.

Syntax:

SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n  
HAVING condition;

MySQL Conditions

There are several conditions in MYSQL such as AND, OR, LIKE, NULL, etc which can be used to filter records according to the desired output lets the syntax and example of each condition

MySQL AND

To test two or more criteria in a single query, use the MySQL AND condition with SELECT, INSERT, UPDATE, or DELETE statements.

Syntax:

WHERE condition1  
AND condition2  
...  
AND condition_n;

Example and Output:

MySQL OR

The MySQL OR condition states that if you have two or more requirements, one of them must be met for the records to be returned.

Syntax:

WHERE condition1  
OR condition2  
...  
OR condition_n;

Example and Output:

MySQL AND OR

You can utilize AND & OR conditions with the SELECT, INSERT, UPDATE, and DELETE statements in MySQL. When combining these requirements, be conscious of where to use round brackets so that the database understands the sequence in which each condition should be evaluated

Syntax:

WHERE condition1  
AND condition2  
...  
OR condition_n;

Example and Output:

MySQL LIKE

The LIKE condition in MySQL is used to find the proper result by pattern matching. It’s used in conjunction with the WHERE clause in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Example and Output:

MySQL IN

In a SELECT, INSERT, UPDATE, or DELETE statement, the MySQL IN condition is used to reduce the use of several OR conditions.

Syntax:

expression IN (value1, value2, .... value_n);

Example and Output:

MySQL NOT

The MySQL NOT condition is the polar opposite of MySQL IN. In a SELECT, INSERT, UPDATE, or DELETE query, it is used to negate a condition.

Syntax:

NOT condition

Example and Output:

MySQL IS NULL

The IS NULL condition in MySQL is used to see if the expression contains a NULL value. It’s utilized with statements like SELECT, INSERT, UPDATE, and DELETE.

Syntax:

expression IS NULL

Example and Output

MySQL IS NOT NULL

The NOT NULL value in the expression is checked using the MySQL IS NOT NULL condition. It’s utilized with statements like SELECT, INSERT, UPDATE, and DELETE

Syntax:

expression IS NOT NULL

Example and Output:

MySQL BETWEEN

The MYSQL BETWEEN condition describes how to extract values from an expression that fall inside a given range.

Example and Output:

MySQL Join

The SELECT command,   It’s used to get data from many different tables. It’s used if you need to get data from two or more tables at the same time.

MySQL joins can be divided into three categories:

• INNER JOIN IN (or sometimes called a simple join)
• MySQL  LEFT JOIN (or sometimes called LEFT OUTER JOIN)
• MySQL  RIGHT JOIN (or sometimes called RIGHT OUTER JOIN)

MySQL Inner JOIN (Simple Join)

Syntax:

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;

Example and Output: