douhan8581
2019-06-10 18:08
浏览 529
已采纳

使用多个where子句选择多个查询

I have two select queries I want to combine for faster performance. I'm a bit confused as I'm not sure if I could use a JOIN or LEFT JOIN or something else to make it faster.

Here are the two queries I want to combine:

Query 1:

SELECT id FROM users WHERE email = :email LIMIT 1

Then, if that row exists from query 1, then run query 2:

SELECT * FROM settings WHERE id = :id_from_query_1

thank you for any kind of help!

图片转代码服务由CSDN问答提供 功能建议

我想要结合两个选择查询以获得更快的性能。 我有点困惑,因为我不确定我是否可以使用 JOIN LEFT JOIN 或其他东西来加快速度。 \ n

以下是我要组合的两个查询:

查询1:

  SELECT id FROM users WHERE email =: 电子邮件LIMIT 1 
   
 
 

然后,如果该行存在于查询1中,则运行查询2:

  SELECT  * FROM设置WHERE id =:id_from_query_1 
   
 
 

感谢您提供任何帮助!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • dougehe2022 2019-06-10 18:17
    已采纳

    When making a join query you can specify what column to join on. In this case it would be:

    select users.id, settings.*
    from users
    join settings on users.id = settings.id
    

    You could also use a double from clause which I think results in the same speed background wise that would look like:

    select users.id, settings.*
    from users, settings
    where users.id = settings.id
    

    If proper indexes are set a join should be faster than 2 selects. Ofcourse there are many factors when it comes to speed like load, network, what is cached and what isn't etc.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duanlujiaji10335 2019-06-10 18:15

    From your description, join should work. Perhaps something like this:

    select u.id, s.* from users as u join settings as s on u.id = s.id where email = ?;
    
    评论
    解决 无用
    打赏 举报
  • duansha6410 2019-06-10 18:15

    Since the 1st subquery will return 1 row (or none):

    SELECT * FROM settings 
    WHERE id = (SELECT id FROM users WHERE email = :email LIMIT 1)
    
    评论
    解决 无用
    打赏 举报
  • dov6891 2019-06-10 18:16
    SELECT u.id 
    FROM users u
    WHERE email = :email 
    LEFT JOIN settings s 
    ON u.id = s.id
    LIMIT 1
    

    Here's the left join query for you.

    评论
    解决 无用
    打赏 举报
  • dongxianji0968 2019-06-10 18:51
    Select s.* from settings s left join users u
    ON u.id = s.id
    Where email = :email
    LIMIT 1;
    

    This will return all the columns from Settings table with that particular ID which has the corresponding "email".

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题