We can use a subquery as a table in the FROM clause in the same way as the result of a subquery can be used with an operator in the WHERE clause. In the following example, we are using the result of subquery as a table by writing it after the FROM clause. It is mandatory to use an alias after subquery, here we are using alias ‘C_car’. To demonstrate it we are using the data as follows from table ‘Cars’ −mysql> Select * from Cars; +------+--------------+---------+ | ID   | Name         | Price   | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID   | Day        | +------+------------+ | 1    | 2017-12-30 | | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from Reserve; +------+------------+ | ID   | Day        | +------+------------+ | 1    | 2017-12-30 | | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ |           1 | Rahul    | |           2 | Yashpal  | |           3 | Gaurav   | |           4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID   | Day        | +------+------------+ |    1 | 2017-12-30 | | ... Read More

With the help of SHOW TABLES From Database_name query, we can see the tables of another database. Here Database_name is the name of the database which we are not using currently. Consider the following example in which we run the query for getting the list of tables in database name ‘tutorial’.mysql> show tables from tutorial; +--------------------+ | Tables_in_tutorial | +--------------------+ | employee           | | showzerofill       | | student            | +--------------------+ 3 rows in set (0.00 sec)

The restriction on MySQL query having a notable list is that it can return, as a result, exactly one row but that result can contain multiple columns.Examplemysql> Select 65/NULL,65+NULL,65*NULL,65-NULL,65%NULL; +------------+--------------+-------------+-------------+---------+ | 65/NULL    | 65+NULL      | 65*NULL     | 65-NULL     | 65%NULL | +------------+--------------+-------------+-------------+---------+ |       NULL |         NULL |        NULL |        NULL |    NULL | +------------+--------------+-------------+-------------+---------+ 1 row in set (0.00 sec)In the above example, we can see that MySQL returns only one row with five columns, having the result of five expressions, as a result when we do not have any table list in the statement.

It is very rarely used to use two columns of the same table in WHERE clause but still we can perform a query with two columns of the same table. Consider the below example −mysql> Select F_name, L_name     -> From Customer     -> where F_name = L_name;     Empty set (0.00 sec)Here we are using both the columns(F_Name and L_Name) from the same table(Customer) hence the result is an Empty set.

As we know that a NULL is no value and it is not the same as zero. MySQL represents a database column as NULL if it does not contain any data. Now, if we will use NULL in any arithmetic expression then the result will be NULL also.Examplemysql> Select 65/NULL, 65+NULL, 65*NULL, 65-NULL, 65%NULL; +---------+---------+---------+---------+---------+ | 65/NULL | 65+NULL | 65*NULL | 65-NULL | 65%NULL | +---------+---------+---------+---------+---------+ |    NULL |    NULL |    NULL |    NULL |    NULL | +---------+---------+---------+---------+---------+ 1 row in set (0.00 sec)From the above example, it can be observed that if we ... Read More

Suppose if we do not want to execute a command that we are entering, then we can use a clear \c option which clears the current input. For example, the use of \c option can be done as follows −mysql> Select *     -> from\cIn the example above, when we use \c in a statement, MySQL clears the current input and returns back to the MySQL prompt for accepting other statements.

With the use of ego, \G option at end of a statement, we can get the result set in vertical format. Consider the following example −mysql> Select * from Student where name = 'Aarav'\G *************************** 1. row ***************************   Name: Aarav RollNo: 150  Grade: M.SC 1 row in set (0.00 sec)