58828

2 Left join for 2 tables?

Question:

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

Answer1:

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 NULL

Answer3:

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.

Recommend

  • 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