Reputation: 21935

MySQL table view limitations

Are there any limitations in the functionality of SQL Views for MySQL?

ex: Can you create a table view using 'JOIN' commands?

Upvotes: 2

Answers (6)

zloctb

Reputation: 11194

Temporary table:

CREATE TEMPORARY TABLE super (id int);

mysql> CREATE OR REPLACE view cat AS SELECT * FROm super;

ERROR 1352 (HY000): View's SELECT refers to a temporary table 'super'

System and local vars:

mysql> SELECT @sosize;//1000

mysql> CREATE OR REPLACE view cat AS SELECT *,@sosize FROm super;
ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Subqueries:

CREATE OR REPLACE view cat AS SELECT * FROm SELECT * FROM super;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

Upvotes: 0

ggiroux

Reputation: 6724

Regarding JOIN, yes:

mysql> create table foo (i int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table bar (i int);
Query OK, 0 rows affected (0.03 sec)

mysql> create view foobar as select foo.i as foo_i, bar.i as bar_i from foo join bar on (foo.i=bar.i);
Query OK, 0 rows affected (0.02 sec)

But as others answers pointed, the manual is a great resource.

Upvotes: 0

Álvaro González

Reputation: 146578

As everything else in SQL, the syntax, features and possibilities depend on the database management system you are working with. But joining tables is pretty basic stuff. Views would not be of much use without it.

Upvotes: 0