alternative syntax using join

Question

I have this query which works fine.

I need the rows where the subcategory belongs to the company

OR

The company has access to default subcategories (c.plannerdefaults =1 )and the subcategory is a default subcategory (s.company =0)

SELECT distinct 
       s.category from planner_subcat s, company c
             where 
               (
                 c.id = 66
                 and c.plannerdefaults = 1
                 and s.company = 0
                )
              or s.company = 66

The thing is, and maybe my thinking is wrong here, I got the impression that if a query starts with

select col from table1, table2

then there is something wrong with the methodology, but in this case I could not think of an alternative using a join.

Is there one?

What do you mean by "there is something wrong with the methodology"? Do you mean in terms of performance? — enigma, Oct 31 '14 at 17:01
I don't see any issue with the query and its logic. you could try to use exists logic to reduce locks — Jaylen, Oct 31 '14 at 17:02
The logic you are using seems fine. You could replace your implicit outer join (your comma) with explicit "OUTER JOIN". To build this with an INNER JOIN would probably get pretty ugly. Either multiple hits against the same tables, or a UNION between two selects, one with an INNER JOIN and and another with an OUTER. — JNevill, Oct 31 '14 at 17:05
@enigma i was just digging through my earlier questions and found [this one](http://stackoverflow.com/questions/22893196/my-sql-select-from-multiple-tables-vs-join) where user juergend said this is 'legacy' syntax, since then i've tried to avoid, although its probably me cargo culting — andrew, Oct 31 '14 at 17:11

Jaylen · Answer 1 · 2014-10-31T17:18:13.693

I am not sure what is your goal here.

Why do you want to re-write the query, are you seeing performance issues?

If you are looking for alternative syntax.

Here is one syntax using sub-query. This query could be a little faster and it will reduce the row locks if the tables are huge (not sure you will have to test it) also id this relation 1 planner-to-many companies then you don't need the DISTINCT function unless it is a different relation then add it back

SELECT s.category 
FROM planner_subcat AS s
WHERE s.company IN(66,0) AND (
s.company = 66 OR EXISTS (SELECT 1 FROM company AS c WHERE id = 66 AND plannerdefaults = 1 AND s.company = 0 AND company = s.company )
)

if you simply want the query to have a newer syntax only then try this

SELECT DISTINCT s.category 
FROM planner_subcat AS s
INNER JOIN company AS c ON c.company = s.company
WHERE s.company IN(0,66) AND ( s.company = 66 OR ( c.id = 66 AND c.plannerdefaults = 1 AND s.company = 0 ) )

But I think my first query will be better in your case since you would not need to use DISTINCT any more. I would think MySQL will not execute the sub query every time unless company = 0 since the company = 66 condition will satisfy the condition then is no reason to do more checking.

from your answer it sounds like the original is ok? if you see my comment to enigma above it may make the reason for the question more clear. I'll give your suggestion a try — andrew, Oct 31 '14 at 17:13
okay. I would suggest you use my first query over your due to the reasons I stated in my answer — Jaylen, Oct 31 '14 at 17:22

score 1 · Accepted Answer · answered Oct 31 '14 at 17:13

1

I would write the query this way:

SELECT s.category
FROM company c
JOIN planner_subcat s
  ON c.id = s.company OR (c.plannerdefaults = 1 AND s.company = 0)
WHERE c.id = 66;

answered Oct 31 '14 at 17:13

Bill Karwin

538,548
86
673
828

This is what I was looking for! I had not though about using a conditional `ON ` clause – andrew Oct 31 '14 at 17:15

alternative syntax using join

2 Answers2