Combining SQL queries without JOIN or UNION

sql
10,934

Solution 1

It might be late reply but below will work

SELECT tblactivate_account.activate_account, tblmembers.members
FROM 
( SELECT COUNT(activate_account.id) activate_account 
WHERE activate_account.email = email ) tblactivate_account,
( SELECT COUNT(members.id) members FROM members
WHERE members.email = email ) tblmembers

modification for nrathaus answer

Solution 2

You need to create two separate queries and join their result not JOIN their tables.

Some pseudo untested SQL but should work:

SELECT tblactivate_account.activate_account, tblmembers.members
FROM 
( SELECT COUNT(activate_account.id) activate_account 
  WHERE activate_account.email = :email ) AS tblactivate_account,
( SELECT COUNT(members.id) FROM members
  WHERE members.email = :email ) AS tblmembers

Solution 3

JOIN and UNION are differents.

In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN. It becomes INNER JOIN if you in your WHERE clause put a link between two table, in your case members and activate_account aren't linked.

UNION can be used when you want get values from two distinct sets. i.e. if you want a list of employees and a list of sailors, supposed, employees and sailors are in two different table.

Share:
10,934
display-name-is-missing
Author by

display-name-is-missing

Updated on November 25, 2022

Comments

  • display-name-is-missing
    display-name-is-missing 12 months

    I need to count rows in two tables, and I would like to make a single SQL-query do the job. After some research I found an solution which I modified to this:

    SELECT COUNT(activate_account.id), COUNT(members.id) FROM members, activate_account 
    WHERE members.email = :email or activate_account.email = :email
    

    Now my question is: is this a good way to combine two SELECT queries, is this just as good solution as if I would have used JOIN or UNION (meaning should there be any peformance improvement compared to if I would have two separate SELECT queries)?