Table of Contents
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:
0 Comments