Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Cross join and Alias in SQL

I want to understand why mySQL says there’s an error in the syntax when I try to run this query:

select g1.*, g2.* 
from (select * from Customer where referee_id is not null) Logs g1, Logs g2

Am I not allowed to alias Logs as g1 and g2?

Tags:

3

Answers


  1. 0

    In your current query:

    select g1.*, g2.* 
from (select * from Customer where referee_id is not null) Logs g1, Logs g2

    It is not clear what Logs g1 is supposed to mean. A table alias in MySQL is a single term/word following either a table name or a subquery (...). In your case, you have a subquery on the Customer table, so this version might make sense:

    select g1.*, g2.* 
from (select * from Customer where referee_id is not null) g1, Logs g2

    Note that if your original goal were a cross join of the Logs table with itself, then you would use the following query:

    select g1.*, g2.* 
from Logs g1, Logs g2
    Login or Signup to reply.
  2. 0

    Perhaps you want a WITH clause (a.k.a. a cte), to cross join.

    WITH Logs AS (
  select * from Customer where referee_id is not null)
select g1.*, g2.* 
from Logs g1, Logs g2

    But I’d rather use the explicit CROSS JOIN syntax:

    WITH Logs AS (
  select * from Customer where referee_id is not null)
select g1.*, g2.* 
from Logs g1 CROSS JOIN Logs g2
    Login or Signup to reply.
  3. 0

    Thanks to @Tim Biegeleisen and @jarlh answers for their answers, this is a combination of both.

    In MySQL, when you want to alias a subquery or a table, you can optionnaly use the AS keyword to specify the alias. The correct syntax for your query would be:

    SELECT g1.*, g2.* 
FROM (SELECT * FROM Customer WHERE referee_id IS NOT NULL) AS Logs g1, 
     (SELECT * FROM Customer WHERE referee_id IS NOT NULL) AS Logs g2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search