3
SELECT
     dim_date.date, dim_locations.city, fact_numbers.metric
FROM
     dim_date, fact_numbers
WHERE
     dim_date.id = fact_numbers.fk_dim_date_id
AND
     dim_locations.city = "Toronto"
AND
     dim_date.date = 2010-04-13;

Since I'm not using the JOIN command, I'm wondering if this is indeed a JOIN (and if not, what to call it)?

This is for a dimensional model by the way which is using surrogate and primary keys to match up details

brasofilo
  • 25,496
  • 15
  • 91
  • 179
Drewdavid
  • 3,071
  • 7
  • 29
  • 53
  • 1
    related: http://stackoverflow.com/a/11180050/1291428; ansi join vs implicit joins – Sebas Oct 26 '13 at 04:35
  • Hi, thanks for sharing that link; it looks as though it's advised to use explicit joins rather than implicit – Drewdavid Oct 26 '13 at 04:43

3 Answers3

2

Yes, it is joining the tables together so it will provide related information from all of the tables.

The one major downfall of linking tables via WHERE instead of JOIN is not being able to use LEFT, RIGHT, and Full to show records from one table even if missing in the other.

However, your SQL statement is invalid. You are not linking dim_locations to either of the other tables and it is missing in the FROM clause.

Your query using an INNER JOIN which is comparable to your WHERE clause may look something like the following:

SELECT DD.date, DL.city, FN.metric
FROM dim_date AS DD
  JOIN fact_numbers AS FN ON DD.id = FN.fk_dim_date_id
  JOIN dim_locations AS DL ON DL.id = FN.fk_dim_locations_id
WHERE DL.city = 'Toronto'
AND DD.date = 2010-04-13
Linger
  • 14,942
  • 23
  • 52
  • 79
2

yes, it is joining tables. It is called non-ANSI JOIN syntax when join clause is not used explicitly. And when join clause is used, it is ANSI JOIN

Kinjal
  • 718
  • 2
  • 11
  • 21
1

If you reference two different tables in a where clause and compare their referential IDs, then yes, it acts the same as a JOIN.

Note however that this can be very inefficient if the optimizer doesn't optimize it properly see: Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL? and INNER JOIN keywords | with and without using them

Community
  • 1
  • 1
Klazen108
  • 690
  • 4
  • 19