CODEWITHSUNDEEP
sqlsqlite
Dan Goodspeed
Dan Goodspeed

Reputation: 3560

Is there any way to simplify these two subqueries into one?

I have the call

SELECT *,
    (SELECT first_name||' '||last_name FROM users WHERE user_id=U.invited_by) AS inviter,
    (SELECT first_name FROM users WHERE user_id=U.invited_by) AS inviter_first
    FROM users AS U

and that works. But as you can see, the two subqueries are both retrieving pretty data from the same row. Is there any way to simplify the two SELECT calls as one and still get the same results?

Upvotes: 0

Views: 39

Answers (1)

Hogan
Hogan

Reputation: 70523

You have to do a join. Since you are joining back to the same table use an alias.

SELECT U.*,
       i_table.first_name||' '||i_table.last_name AS inviter,
       i_table.first_name as inviter_first
FROM users as U
LEFT JOIN users as i_table on i_table.user_id=U.invited_by

Note this query changes your query from performing 2 queries per row (so 2n * n or O(n^2)) to performing 1 joined query.

If you have an index on user_id you should see amazing increases in performance.

If you don't it should still be a lot faster at O(2n)

Upvotes: 4

Related Questions