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

In both the cases i.e. on not using ‘RIGHT’ or ‘LEFT’ keyword in the query, MySQL will return the result by taking it as INNER JOIN query. It is because the only difference between RIGHT, LEFT and INNER JOIN is the keyword of RIGHT or LEFT. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data −mysql> Select * from tbl_1; +----+--------+ | Id | Name   | +----+--------+ | 1  | Gaurav | | 2  | Rahul  | | 3  | Raman  | | 4  | Aarav  | +----+--------+ ... Read More

Basically, SOUNDEX() function is used to return the Soundex, a phonetic algorithm for indexing names after English pronunciation of sound, a string of a string. The proper structure for searching within MySQL using SOUNDEX() is as follows −SOUNDEX(Str)Here, Str is the string whose SOUNDEX string is to be retrieved.Examplemysql> Select SOUNDEX('MySQL'); +------------------+ | SOUNDEX('MySQL') | +------------------+ | M240             | +------------------+ 1 row in set (0.00 sec) mysql> Select SOUNDEX('harshit'); +--------------------+ | SOUNDEX('harshit') | +--------------------+ | H623               | +--------------------+ 1 row in set (0.00 sec) mysql> ... Read More