Its very important to create MySQL view from two or more tables with different column names. It is not mandatory but we sometime need it hardly. This can be done using UNION each table. Union's must have same columns in the same order across all sections. You should explicitly select/declare the null columns in each part of the union. You can use normal JOIN on each part.
Views in MySQL are two types: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases and behave as well as the base table. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. It cause some performance impact.
The default option is UNDEFINED, which determined by MySQL to select the appropriate algorithm. MySQL first try to use MERGE because it is more efficient than TEMPTABLE.
If the MERGE algorithm cannot be used (determined by MySQL itself), a temporary table must be used instead (This algorithm is TEMPTABLE). MERGE cannot be used if the view contains any of the following constructs (Aggregate function or UNION):
SUM
AVG
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL
And the final example is as follows:
CREATE OR REPLACE ALGORITHM=MERGE VIEW MY_VIEW AS
SELECT test1.id AS _id, test1.name AS _name, test1.roll AS _roll from test1
UNION
SELECT test2.id AS _id, test2.type AS _name, NULL AS _roll from test2
Views in MySQL are two types: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases and behave as well as the base table. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. It cause some performance impact.
The default option is UNDEFINED, which determined by MySQL to select the appropriate algorithm. MySQL first try to use MERGE because it is more efficient than TEMPTABLE.
If the MERGE algorithm cannot be used (determined by MySQL itself), a temporary table must be used instead (This algorithm is TEMPTABLE). MERGE cannot be used if the view contains any of the following constructs (Aggregate function or UNION):
SUM
AVG
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL
And the final example is as follows:
CREATE OR REPLACE ALGORITHM=MERGE VIEW MY_VIEW AS
SELECT test1.id AS _id, test1.name AS _name, test1.roll AS _roll from test1
UNION
SELECT test2.id AS _id, test2.type AS _name, NULL AS _roll from test2
