MySQL – Returning Information in Nested Select in Outermost Select

MySQL

I've got the following working,

SELECT info1, info2 
FROM   TableA 
WHERE  id IN (SELECT xid 
              FROM   TableB 
              WHERE  userid=3 AND active=1)

which works fine, but in TableB I also have a team column which I'd like to select and return in the outermost select statement, something like this "pseudo" query, which of course doesn't work:

SELECT info1, info2, team 
FROM   TableA 
WHERE  id IN (SELECT xid, team 
              FROM   TableB 
              WHERE  userid=3 AND active=1)

I had a read around and thought maybe AS could help but I don't really know for sure. Can anybody suggest a solution? Is this even possible?

Best Answer

This is what JOINs are for:

SELECT  A.info1, 
        A.info2, 
        B.team 
FROM TableA A
INNER JOIN TableB B
    ON A.id = B.xid
WHERE B.userid = 3 
AND B.active=1
;

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.

Mysql – Subselect dependent on first select value

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL 

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.

Best Answer

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

Mysql – Subselect dependent on first select value

Related Question