如何在 MySQL LEFT JOIN 中使用别名

How to use aliases with MySQL LEFT JOIN

提问by styfle

My original query is doing joins using the WHERE clause rather than JOIN. I realized that this was not returning movies that did not have any stars or genres did not show up so I think I have to do a LEFT JOIN in order to show every movie. Here is my original SQL:

我原来的查询是使用 WHERE 子句而不是 JOIN 进行连接。我意识到这不是回归没有任何明星或流派的电影没有出现，所以我想我必须做一个左连接才能显示每部电影。这是我的原始 SQL：

SELECT *
FROM movies m, stars s, stars_in_movies sm, genres g, genres_in_movies gm
WHERE m.id = sm.movie_id
AND sm.star_id = s.id
AND gm.genre_id = g.id
AND gm.movie_id = m.id
AND m.title LIKE '%the%'
AND s.first_name LIKE '%Ben%'
ORDER BY m.title ASC
LIMIT 5;

I tried to do a LEFT JOIN on movies I'm definitely doing something wrong.

我试图对电影进行 LEFT JOIN 我肯定做错了什么。

SELECT *
FROM movies m, stars s, stars_in_movies sm, genres g, genres_in_movies gm
LEFT JOIN movies m1 ON m1.id = sm.movie_id
LEFT JOIN movies m2 ON m2.id = gm.movie_id
AND sm.star_id = s.id
AND gm.genre_id = g.id
ORDER BY m.title ASC
LIMIT 5;

I get ERROR 1054 (42S22): Unknown column 'sm.movie_id' in 'on clause'so clearly I'm doing the join wrong, I just don't see what it is.

我ERROR 1054 (42S22): Unknown column 'sm.movie_id' in 'on clause'很清楚我做错了，我只是不明白它是什么。

采纳答案by Mark Byers

Don't mix the comma operator with JOIN- they have different precedence! There is even a warning about this in the manual:

不要将逗号运算符与JOIN- 它们具有不同的优先级！手册中甚至有关于此的警告：

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause'may occur. Information about dealing with this problem is given later in this section.

但是，逗号运算符的优先级小于 INNER JOIN、CROSS JOIN、LEFT JOIN 等。如果在有连接条件的情况下将逗号连接与其他连接类型混合使用，Unknown column 'col_name' in 'on clause'可能会出现格式错误。本节稍后将提供有关处理此问题的信息。

Try this instead:

试试这个：

SELECT *
FROM movies m
LEFT JOIN (
   stars s
   JOIN stars_in_movies sm
       ON sm.star_id = s.id
) ON m.id = sm.movie_id AND s.first_name LIKE '%Ben%'
LEFT JOIN (
    genres g
    JOIN genres_in_movies gm
        ON gm.genre_id = g.id
) ON gm.movie_id = m.id
WHERE m.title LIKE '%the%'
ORDER BY m.title ASC
LIMIT 5;

回答by Chris J

You should put your conditions related to your JOINs in the same ON clause. However, for your above problem, you should use the following query:

您应该将与 JOIN 相关的条件放在同一个 ON 子句中。但是，对于上述问题，您应该使用以下查询：

SELECT *
FROM movies m 
LEFT JOIN stars_in_movies sm ON sm.movie_id = m.id
JOIN stars s ON sm.star_id = s.id
LEFT JOIN genres_in_movies gm ON gm.movie_id = m.id
JOIN genres g ON gm.genre_id = g.id
ORDER BY m.title ASC
LIMIT 5;

回答by Vignesh

Maybe ugly, But the way it will work is here. Beware this is ugly and lot of people is giving warning about this kind of hacks

也许丑陋，但它的工作方式就在这里。当心这是丑陋的，很多人都在警告这种黑客行为

SELECT *
FROM movies m, stars_in_movies sm LEFT JOIN movies m1 ON m1.id = sm.movie_id, stars s
ORDER BY m.title ASC
LIMIT 5;

when using joins, you must do the join with the right table which have the columns you are comparing.

使用连接时，您必须与包含要比较的列的正确表进行连接。

回答by Rahul Panwar

SQL Join (inner join in MySQL)

SQL Join（MySQL 中的内部联接）

select emp1.id,emp1.name,emp1.job  from (select id, type as name, description as job from component_type as emp1)emp1
inner join 
emp
on emp1.id=emp.id;

Left Join

左加入

select emp1.id,emp1.name,emp1.job  from (select id, type as name, description as job from component_type as emp1 where id between '1' AND '5')emp1
left join 
emp
on emp1.id=emp.id;

Right Join

右加入

select emp1.id,emp1.name,emp1.job  from (select id, type as name, description as job from component_type as emp1)emp1
Right join 
(select * from emp where id between '1' and '5')exe
on emp1.id=exe.id;

回答by pradip kor

Using alias connect many table without using join..

使用别名连接多个表而不使用连接..

select sum(s.salary_amount) as total_expenses_paid_to_all_department
from salary_mas_tbl s,dept_mas_tbl d
where s.salary_dept=d.dept_id;