WHERE and JOIN statements, which is better?

Question

I am currently working with a lot of SQL database extraction data for a special project I have. I see most of statement used by programmers are the WHERE statements to join tables than the inner, left or right join statements. My question is to extract data or data consolidation between 2 tables which is better WHERE or JOINS?

Answer 1

Ruben F. Villanueva Viana, can you post the tables' structure (and some of its records if possible) and your SQL query?

IIRC the LEFT or RIGHT join may allow broken reference records to be selected (nulls are used in such cases), INNER join on the other hand ensures only records that have valid reference are selected.

Answer 2

They have different purpose all to themselves. WHERE clause defines condition(s) that must be matched to declare that record(s) is eligible to be included in selected data, but it does not join tables, you can specify a condition to check from different tables, but still, it differs with JOIN.

To have a solid key references validity amongst the tables involved you can use INNER JOIN, it ensures that only valid foreign -> primary references included in the results.

Answer 3

Ipan I have a case were I have to tables lets tabla A wth table B, my look up values are in B and made a left join to see all the data from B and those that match A I know some values are not in A but still when I do the query I get all the values that match both tables? why do you think thst is ?

Answer 4

The WHERE clause pattern that in the original post is similar to an inner join
It is a lazy risky option
It might be ok for ad hoc queries, but the criteria to match the rows in the tables and the criteria to filter the overall result set are altogether. This gets confusing and as the query is edited, the table match may get edited too. Not a risk I like to take so I use INNER JOIN not just putting the join criteria in the WHERE clause