How can we create a MySQL view by using data from multiple tables?

MySQL UNION operator can combine two or more result sets hence we can use UNION operator to create a view having data from multiple tables. To understand this concept we are using the base tables ‘Student_info’ and ‘Student_detail’ having the following data −

mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Chandigarh | Literature | | 125  | Raman   | Shimla     | Computers  | | 130  | Ram     | Jhansi     | Computers  | | 132  | Shyam   | Chandigarh | Economics  | | 133  | Mohan   | Delhi      | Computers  | +------+---------+------------+------------+ 6 rows in set (0.00 sec) mysql> Select * from Student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ |       100 | Gaurav      | Delhi      | |       101 | Raman       | Shimla     | |       103 | Rahul       | Jaipur     | |       104 | Ram         | Chandigarh | |       105 | Mohan       | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.00 sec)

Example

The query below will create a view by using the data from both the above-mentioned tables −

mysql> Create or Replace View Info AS Select StudentName from Student_detail UNION Select Name From Student_info; Query OK, 0 rows affected (0.10 sec) mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav      | | Raman       | | Rahul       | | Ram         |   | Mohan       | | YashPal     | | Shyam       | +-------------+ 7 rows in set (0.00 sec)

The above result set has the combination of values from both the columns. If a value is repeating then it eliminates the duplicate values.

We can also stores all the values, duplicate one also by using the UNION ALL as in the following query −

mysql> Create or Replace View Info AS Select student name from Student_detail UNION ALL Select Name From Student_info; Query OK, 0 rows affected (0.16 sec) mysql> select * from info; +-------------+ | StudentName | +-------------+ | Gaurav      | | Raman       | | Rahul       | | Ram         | | Mohan       | | YashPal     | | Gaurav      | | Raman       | | Ram         | | Shyam       | | Mohan       | +-------------+ 11 rows in set (0.00 sec)