CODEWITHSUNDEEP
sqlmysql
anon
anon

Reputation:

2 Left join for 2 tables?

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.

Example:

table portal:

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.

Example:

SELECT * 
  FROM portal,secondary_table s
 WHERE portal.id=s.portalid 
   AND type of secondary_table is 4 and is not 5

Results:

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 help

Upvotes: 0

Views: 191

Answers (3)

dynamic
dynamic

Reputation: 48101

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.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

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.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

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 NULL

Upvotes: 1

Related Questions