I am on MySQL:
I have 2 table, one is the main table, the other is an accessory table that contains some information supporting the records of the main table.
id title desc 12 "aaa" "desc" 13 "bbb" "desc" [etc]
secondary table (omitting the primary id field)
type portalid x 12 2 12 3 12 4 12 5 12 1 13 2 13 4 13
I need to select every record in the table portal that got a record in the secondary table with type = 4 but != 5.
SELECT * FROM portal,secondary_table s WHERE portal.id=s.portalid AND type of secondary_table is 4 and is not 5
In this case only the record 13 of portal should be returned because the record 12 got both type 4 and 5.
Please note I asked a similar question but considering only one table, and with that query took over 50 secs to be elaborated.
Thanks for any helpAnswer1:
You should consider rephrasing it using NOT EXISTS clauses. If all you want are records from
portal, then a double EXISTS clause will work and very clearly reveal the query intentions
SELECT * FROM portal WHERE EXISTS (select * from secondary_table s1 where portal.id=s1.portalid and s1.type=4) AND NOT EXISTS (select * from secondary_table s2 where portal.id=s2.portalid and s2.type=5)
However, due to how MySQL process EXISTS clauses (even though it is clearer), you can trade off clarify for performance using LEFT JOIN / IS NULL. Please read the following link, however the performance of each query may vary with specific data distribution, so try both and use whichever works better for your data.
<a href="http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/" rel="nofollow">NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL</a>
The LEFT JOIN / IS NULL form would be written
SELECT * FROM portal JOIN secondary_table s1 ON portal.id=s1.portalid and s1.type=4 LEFT JOIN secondary_table s2 ON portal.id=s2.portalid and s2.type=5 WHERE s2.portalid IS NULL
The order of the tables (portal, inner, left) is to allow processing the first two tables (portal + secondary/type=4) and trimming the result set early before launching into the LEFT (outer) JOIN (that retains everything from the left side) for the existential test.Answer2:
This is why you should avoid the older
FROM A,B syntax - it's less powerful with respect to certain things. Use explicit join types (LEFT/RIGHT/INNER/FULL/CROSS) instead.
SELECT <columns> FROM portal p LEFT JOIN secondary s1 ON p.id=s1.portalid AND s1.type = 5 INNER JOIN secondary s2 ON p.id=s2.portalid AND s2.type = 4 WHERE s1.type IS NULLAnswer3:
I will use this query that's very similar to EXISTS of richard:
SELECT * FROM portal WHERE id IN (SELECT portalid FROM sec WHERE type=4) AND id NOT IN (SELECT portalid FROM sec WHERE type=5)
imo it's even more readable.