If we want to compare the data values of two columns then we need to provide the name of the columns as arguments of MySQL STRCMP() function. Suppose we have a table named ‘Marks’ which contains the name of the student and their secured marks in different subjects. Now, if we want to know that a particular student has got more or less or equal marks in two subjects then it can be illustrated using STRCMP() function as follows −Examplemysql> Select Name, STRCMP(Math, Hindi) from student marks WHERE Name = 'Rahul'; +-------+--------------------+ | Name  | STRCMP(Math, Hindi) | +-------+--------------------+ | ... Read More

For the purpose of comparison, we can use number values as an argument in STRCMP() function. They are given as arguments without quotes. Following example will demonstrate it.Examplemysql> Select STRCMP(10, 10)As 'Equal Numbers', STRCMP(11, 10)AS '2nd Smaller', STRCMP(10, 11)AS '1st Smaller', STRCMP(10, NULL)As '2nd NULL', STRCMP(NULL, 10)AS '1st NULL', STRCMP(NULL, NULL)AS 'Both NULL'; +---------------+-------------+-------------+----------+----------+-----------+ | Equal Numbers | 2nd Smaller | 1st Smaller | 2nd NULL | 1st NULL | Both NULL | +---------------+-------------+-------------+----------+----------+-----------+ | 0             | 1           | -1          | NULL     | ... Read More

Since we cannot use MINUS query in MySQL, we will use LEFT JOIN to simulate the MINUS query. It can be understood with the help of the following example:ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name    | Address    | Subject    | +-----------+---------+------------+------------+ | 101       | YashPal | Amritsar   | History    | | 105       | Gaurav  | Chandigarh | Literature | | 130       | Ram     | Jhansi   ... Read More

Suppose we have the following table named ‘stock_item’ in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ as shown in the table.mysql> Select * from stock_item; +------------+----------+ | item_name  |quantity  | +------------+----------+ | Calculator |       89 | | Notebooks  |       40 | | Pencil     |       40 | | Pens       |       32 | | Shirts     |       29 | | Shoes      |   ... Read More

In this case, SPACE() function would add white spaces depending upon the condition given in WHERE clause. The following example from student table will demonstrate it.Examplemysql> Select Id,Name,Space(5) from student WHERE Name='Harshit'; +------+---------+----------+ | Id   | Name    | Space(5) | +------+---------+----------+ | 15   | Harshit |          | +------+---------+----------+ 1 row in set (0.00 sec)

MySQL SPACE() function is used to add white spaces between two strings. The argument passed in SPACE() function is an integer which specifies how many white spaces we want to add.SyntaxSPACE(N)Here, N is an integer specifies the number of white spaces we want to add.Examplemysql> Select 'My Name is', Space(5), 'Ram'; +------------+----------+-----+ | My Name is | Space(5) | Ram | +------------+----------+-----+ | My Name is |          | Ram | +------------+----------+-----+ 1 row in set (0.00 sec)In the example above, SPACE() function adds 5 white spaces between the strings.

We can get the differences between the tables by unioning exclusion joins from 1st table to 2nd table and from 2nd table to 1st table. To understand it, we are taking the example of following two tables −mysql> Select * from value1; +-----+-----+ | i   | j   | +-----+-----+ |   1 |   1 | |   2 |   2 | +-----+-----+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i    | j    | +------+------+ |    1 |   1  | |    3 |   3 ... Read More

Actually, INTERSECTION is just an inner join on all columns. We are taking a simple example of two tables, having the data as follows −mysql> Select * from value1; +------+------+ | i    | j    | +------+------+ | 1    | 1    | | 2    | 2    | +------+------+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i    | j    | +------+------+ | 1    | 1    | | 3    | 3    | +------+------+ 2 rows in set (0.00 sec)Now, the following query will do the INTERSECTION between these tables −mysql> Select * from value1 join value2 using(i,j); +------+------+ | i    | j    | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.08 sec)

It can be understood with the help of an example in which two tables are having some values and we subtract the values with the help of LEFT JOIN. Here we are taking two tables having the following data −mysql> Select * from value_curdate; +----+----------+-------+ | Id | Product  | Price | +----+----------+-------+ | 1  | Notebook | 100   | | 2  | Pen      | 40    | | 3  | Pencil   | 65    | +----+----------+-------+ 3 rows in set (0.00 sec) mysql> Select * from value_prevdate; +----+-----------+-------+ | Id | Product   | ... Read More

Actually, sometimes we can avoid data-driven relationships in tables and we need to join them. It can be done with the help of CASE statement in the SELECT list to handle the joining possibilities. To understand it, we are taking the example of three data-driven tables namely ‘Student_Detail’ which have the following data −mysql> Select * from student_detail; +----+---------+ | Id | Name    | +----+---------+ | 1  | Harshit | | 2  | Rahul   | | 3  | Aarav   | +----+---------+ 3 rows in set (0.00 sec)Now, we have the three tables namely ‘Student_Harshit’, ‘Student_Rahul’, ‘Student_Aarav’ which ... Read More