0

I am trying to dynamically build query that should look like this:

SELECT * 
FROM candidate_info 
INNER JOIN candidate_job ON info_uniqid = job_info_uniqid 
INNER JOIN candidate_status ON info_uniqid = status_info_uniqid 
WHERE 1 = 1
AND status_status = :status_status

Accidentally I discovered that if I omit the WHERE clause the query works just fine:

SELECT * 
FROM candidate_info 
INNER JOIN candidate_job ON info_uniqid = job_info_uniqid 
INNER JOIN candidate_status ON info_uniqid = status_info_uniqid 
AND status_status = :status_status

Can you please help me understand:

  1. Why is the query still working?
  2. What are the risks of using the query without the WHERE clause?

Note: WHERE 1 = 1 is chosen for its simplicity.

Thank you very much!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Mihai
  • 2,807
  • 4
  • 28
  • 53

2 Answers2

2

The query is working because you have moved the condition from the WHERE clause to the ON clause.

Because you are working with INNER JOIN, the effect is exactly the same. The two queries do the same thing and should have the same execution plan.

I would suggest that you qualify your column names, so it is clear what table they come from. I am guessing the column comes from candidate_status. If so, putting it in the on clause is more a question of style.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What I am actually trying to do is to join three tables based on a common id and then fetch data based on what status the user is selecting from a drop-down menu. Your answer helped me out. Thank you! – Mihai Jan 31 '16 at 17:52
0

You can add more then one condition to JOIN ON, so that is what you are doing. You 

INNER JOIN candidate_status ON
(
  info_uniqid = status_info_uniqid AND status_status = :status_status
)
luksch
  • 11,497
  • 6
  • 38
  • 53