UNION, CROSS & SELF JOIN

In simple terms, Joins combine data into new columns. Today I am gonna discuss in detail with Union, Cross & Self Join. And this part is the last & complete part of whole Joins that I discussed with another previous two-part. Before continuing this final part you can check part-1 & part-2 of the whole Join discussion. MySql Join, Inner Join, Left Join are in part-1 and Right Join & Full Join described in part-2.

UNION JOIN

I elaborately explain UNION & UNION ALL operator for MySql Joining. MySql Union operator used to combine two or more SQL statements in one result set. By default, it will return the distinct result set. If you want to return all the result-set then you have to use MySql UNION ALL operator. I will give some examples of UNION & UNION ALL operator. I have shared a sample practical database in the previous part. So, please check the sample database for practice. The Union operator syntax is

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

In terms of sample database then SQL Statement is for UNION:

• 

• 

Above result-set, there is a total of 6 rows. Do you know who MySql Union operator works? Returns all the single statement results, combine them & finally remove duplicates. Therefore here the above statement, Firstly, two SQL statement returns all the country names from the customer & supplier table. Secondly, combine total result-set and finally remove all the duplicates coz MySql Union returns the only distinct result. This is the procedure of MySql UNION Operator.

For UNION ALL operator the syntax is:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

And the SQL Statement for UNION ALL is:

and the result is

We are getting a total 15 rows result-set. 10 rows from customer & 5 rows from the supplier table. UNION ALL operator grab all customer’s country & supplier’s country then combine. Not removing duplicates coz UNION ALL will not return distinct rows.

CROSS JOIN or Cartesian Product

CROSS JOIN is known as Cartesian Product. This type of JOIN returns Cartesian Product of rows from the table. In MySql, CROSS JOIN behaves like INNER JOIN without using any condition.

CROSS JOIN returns the cartesian product. In MySql, CROSS JOIN behaves like INNER JOIN without using any condition. Actually, The CROSS JOIN returns a result which is the number of rows in the first table that is multiplied by the number of rows in the second table if there is no WHERE clause is used with CROSS JOIN. This kind of result is called the Cartesian Product.

The syntax is CROSS JOIN

SELECT * FROM table1
CROSS JOIN
table2;

And the SQL Statement is:

Result

See this result-set. Here I already said that a single row of the first column is multiple by all rows of the second column. See the id section. There are five rows on the left side with red border says, all are the same data. I mean all five records are in from first table and multiple with the second table all rows displayed in right side red border.

SELF JOIN

SELF JOIN means join tables with itself. Therefore, the syntax is:

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

The SQL Statement is:

Here customerTable and supplierTable are treated different table aliases for the same table.

Conclusion

Finally, In this tutorial, we have learned lots of things about MySql Joins with some practical database examples. MySql Joins are not only used for two tables but also You can use it for two or more tables for the relational database. Joins are too much faster for getting the result than single multiple queries. Basically you can use it to create a report in which data is located with a different relational table. So, That’s it for finally.

THANKS & ENJOY YOU LEARNING.