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.


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.


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 help


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.


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


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.


  • Doxygen Documentation for WPF
  • How can I write a generator in a JavaScript class?
  • Update timer inside div. JavaScript
  • getRestApiId function in objects from office-js for OneNote
  • Jquery ScrollTop to multiples ID + SetTimeout
  • System.Timers.Timer (.NET) really slow?
  • How to render a pixel array most efficiently to a window in c++?
  • abstracting over a collection
  • Find group of records that match multiple values
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • TFS 2015 - Waiting for an agent to be requested
  • Redirect STDERR in OPEN pipe comand. Perl Linux
  • Remove changes from one element when event occurs on another element?
  • RxJava debounce by arbitrary value
  • Android Google Maps API v2 start navigation
  • how to avoid repetitive constructor in children
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Groovy: Unexpected token “:”
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • How to create a file in java without a extension
  • Is it possible to access block's scope in method?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Spark fat jar to run multiple versions on YARN
  • Ajax Loaded meta Tags
  • SignalR .NET Client Invoke throws an exception
  • Xamarin Forms - UWP Fonts
  • Insert into database using onclick function
  • MySQL WHERE-condition in procedure ignored
  • Knitr HTML Loop - Some HTML output, some R output
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • ActionScript 2 vs ActionScript 3 performance
  • Display Images one by one with next and previous functionality
  • How to pass list parameters for each object using Spring MVC?
  • Arrays break string types in Julia
  • json Serialization in asp
  • SQL merge duplicate rows and join values that are different
  • WPF Applying a trigger on binding failure
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • JaxB to read class hierarchy