How to use aliases in MySQL natural full outer join?

Question

Here is my code,when I run this, it will show an error: every derived table must have an alias, however I try different methods to add aliases, it just doesn't work.Anyone know how to fix it ?

SELECT *
FROM (SELECT * FROM student s WHERE dept_name='Comp.Sci')
NATURAL FULL OUTER JOIN
(SELECT * FROM takes t WHERE semester='Spring' AND YEAR=2009);

Answer 1

in mysql there is not full outer join but you could use union (eventually show us a proper datasample for suggest the better query union)

    SELECT * FROM student s WHERE dept_name='Comp.Sci'
    union
    SELECT * FROM takes t WHERE semester='Spring' AND YEAR=2009

For your code you need an alias for each table eg: for each from()

    SELECT *
    FROM (SELECT * FROM student s WHERE dept_name='Comp.Sci') t1
    JOIN ........
    (SELECT * FROM takes t WHERE semester='Spring' AND YEAR=2009) t2;

anyway if you have same columns name in the derived tabke you cant use * (select all) but use explicit column name and alias for avoid ambiguity