MySQL Topics That Can Be Missed

Summary: MySQL is really big there are a lot of topics which can be missed following are some of things tha can be missed.

MySQL – Derived Table

Derived tables, also known as subqueries or inline views, are temporary result sets that can be referenced within the scope of a SELECT, INSERT, UPDATE, or DELETE statement.

SELECT ID, NAME, SALARY FROM 
(SELECT * FROM CUSTOMERS) 
AS DERIVED_CUSTOMERS;

SELECT ID, NAME, SALARY FROM 
(SELECT * FROM CUSTOMERS) 
AS DERIVED_CUSTOMERS 
WHERE DERIVED_CUSTOMERS.SALARY > 5000.00;

SELECT ID AS DERIVED_ID, NAME AS DERIVED_NAME, 
SALARY AS DERIVED_SALARY FROM 
(SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

MySQL Constraints

The MySQL constraints can be used to set certain rules to the column(s) in a table. These constraints can restrict the type of data that can be inserted or updated in a particular column. This helps you to maintain the data accuracy and reliability in a table.

MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint in MySQL is used to link a field or collection of fields in one table to the primary key of another table.

A table with the foreign key is called a child table and the table with the primary key is called the parent table or referenced table.

CREATE TABLE CUSTOMERS (
CUST_ID int NOT NULL, 
NAME varchar(20) NOT NULL, 
AGE int, 
PRIMARY KEY (CUST_ID) 
);

CREATE TABLE ORDERS ( 
ORDER_ID int NOT NULL, 
ORDER_NUMBER int NOT NULL, 
CUST_ID int, 
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS (CUST_ID)
);

MySQL CHECK Constraint

The CHECK constraint in MySQL restricts the range of values that can be inserted into a column. This constraint ensures that the inserted value in a column must be satisfied with the provided condition.

CREATE TABLE CUSTOMERS ( 
ID int NOT NULL, 
NAME varchar(20) NOT NULL, 
AGE int, 
CHECK (AGE >= 18) 
);

MySQL Insert on Duplicate Key Update Statement

When we are trying to insert a new row into a MySQL table column with a UNIQUE INDEX or PRIMARY KEY, MySQL will issue an error, if the value being inserted already exists in the column. This will happen because these constraints require unique values, and duplicate values are not allowed.

However, if we use the MySQL ON DUPLICATE KEY UPDATE clause with with the INSERT INTO statement, MySQL will update the existing rows with the new values instead of showing an error.

INSERT INTO CUSTOMERS 
(ID, NAME, AGE, ADDRESS, SALARY) 
VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00) 
ON DUPLICATE KEY UPDATE 
NAME = "Chaitali", AGE = 25,
ADDRESS = "Mumbai", SALARY = 6500.00;

MySQL Create View Statement

Creating a view is simply creating a virtual table using a query. A view is an SQL statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query.

CREATE VIEW view_name AS 
select_statements FROM table_name;

Type of Index

Indexes can be defined on single or multiple columns of a MySQL table. The decision to add indexes on a table column(s) depends on the type of data that needs to be searched. MySQL provides the following types of indexes −

• Simple Index
• Unique Index
• Primary Key Index
• Fulltext Index
• Descending Index

Simple Index

A simple index is a basic type of index where the values inserted into the column, containing this index, are searched easily. In such case, the column can contain duplicate values or NULL.

Unique Index

A Unique index does not allow any duplicate values to be inserted into a table column (where the index is defined on).It can be added to single or multiple columns of a table. If it is added to a single column, the values of that column must be unique. But if it is added to multiple columns, the combination of values in these columns must be unique.

Primary Key Index

Primary Key Index is an extension of unique index, as the primary key column must always contain unique values and these values must not be NULL. Primary key can be set to a single column of a database table, or multiple columns as well (which is not recommended).

Fulltext Index

In a database, sometimes you would have to search for a blob of text instead of a record. You can use fulltext index for it. As its name suggests, it is used to make the text searches in a table easier.

Descending Index

The descending index is only available in MySQL versions after 8.0. It is simple index used to store data in a reverse order. Using this index, it is easy to search for the latest values inserted into the database table.

Creating Indexes on Existing Table

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

MySQL Having Clause

The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.

This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

SELECT NAME, SUM(SALARY) 
as total_salary FROM CUSTOMERS 
GROUP BY NAME 
HAVING SUM(SALARY) < 4540 ORDER BY NAME;

MySQL LIKE Operator

Now, we are retrieving the name of the customers ending with “esh” using the LIKE operator with wildcards −

SELECT*from CUSTOMERS WHERE NAME LIKE'%esh';

The following query retrieves the customers whose names start with ‘M’ and ‘R’ −

SELECT*FROM CUSTOMERS 
WHERE Name LIKE'M%'OR Name LIKE'R%';

MySQL In Operator

The IN operator in MySQL is a logical operator that allows us to check whether the values in a database are present in a list of values specified in the SQL statement.

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. It returns all rows in which the specified column value matches any one of the values in the list.

SELECT * FROM CUSTOMERS 
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');

MySQL NOT EQUAL Operator

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn’t. So, it is recommended to use the <> operator.

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: The comparison is case-sensitive by default when using this operator with text values.

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table.

MySQL Inner Join

MySQL Inner Join is a type of join that is used to combine records from two related tables, based on common columns from both the tables. These tables are joined together on a specific condition. If the records in both tables satisfy the condition specified, they are combined.

This is a default join; that is, even if the JOIN keyword is used instead of INNER JOIN, tables are joined using matching records of common columns. They are also referred to as an Equijoin.

SELECT ID, NAME, AMOUNT, DATE FROM 
CUSTOMERS INNER JOIN ORDERS ON 
CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

MySQL Left Join

Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table.

If the records in left table do not have their counterparts in the second table, NULL values are added.

But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result.

SELECT ID, NAME, AMOUNT, DATE FROM 
CUSTOMERS LEFT JOIN ORDERS ON 
CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

MySQL Right Join

The Right Join or Right Outer Join query in MySQL returns all rows from the right table, even if there are no matches in the left table. So, if zero records are matched in the left table, the right join will still return a row in the result, but with a NULL value in each column of the left table.

In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

The resultant table displayed after implementing the Right Join is not stored anywhere in the database.

MySQL Cross Join

A MySQL Cross Join combines each row of the first table with each row of second table. It is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables (i.e. permutations).

SELECT ID, NAME, AMOUNT, DATE FROM 
CUSTOMERS CROSS JOIN ORDERS;

MySQL Full Join

MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.

In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results.

The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword.

But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred.

SELECT ID, NAME, AMOUNT, DATE FROM 
CUSTOMERS LEFT JOIN ORDERS ON 
CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
UNION 
SELECT ID, NAME, AMOUNT, DATE FROM 
CUSTOMERS RIGHT JOIN ORDERS ON 
CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

MySQL Self Join

The MySQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, at least one table is temporarily renamed in the MySQL statement.

Self Join is a type of inner join, which performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it.

However, unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause.

SELECT a.ID, b.NAME as EARNS_HIGHER, 
a.NAME as EARNS_LESS, a.SALARY as 
LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b 
WHERE a.SALARY < b.SALARY;