MySql - Where do you need to list the tables from which to extract data from?

Question

I thought that you needed to list the tables that you would be working with in a FROM clause as in:

SELECT * FROM a, b WHERE a.id = b.id

I am learning about joins and saw the following:

SELECT a.account_id, a.cust_id, a.open_date, a.product_cd
FROM account a INNER JOIN employee e
    ON a.open_emp_id = e.emp_id
        INNER JOIN branch b
        ON e.assigned_branch_id = b.branch_id
        WHERE e.start_date < '2007-01-01'
        AND (e.title = 'Teller' OR e.title = 'Head Teller')
        AND b.name = 'Woburn Branch';

Only two tables are listed here in the FROM clause as follows:

FROM account a INNER JOIN employee e

Yet another table is listed in another join clause that is outside the FROM clause as follows:

INNER JOIN branch b

My first thought was that an invalid syntax error would be thrown because the branch table was not listed in the FROM clause, but I was wrong.

What are the rules when listing the tables you will be extracting data from. Any links to official docs would greatly help.

From a, b, c === From a inner join b on .. inner join c on ... (FROM ... default is inner join). You only need to include tables used in select or where — SIDU, Jan 27 '16 at 00:16
Yeah, you've got that completely wrong. Joins can be written implicitly (FROM x,y,x WHERE x.a = y.a, etc) or explicitly (FROM x JOIN y... ON... JOIN z.... ON...). Never use the first method. — Strawberry, Jan 27 '16 at 00:20
Pretty much everything between `FROM` and `WHERE` is "inside the from"... so long as the `WHERE` we're talking about isn't in a subquery "inside the from" [offical docs](http://dev.mysql.com/doc/refman/5.7/en/select.html) — Uueerdo, Jan 27 '16 at 00:52
@SIDU When using the horrendous `,` notation technically everything in the from is a `CROSS JOIN`; it is the `WHERE` conditions that commonly go along with it that change that. — Uueerdo, Jan 27 '16 at 00:54
Join via comma (",") and "explicit" joins via keyword JOIN and any ONs are all part of the FROM. See my answer PS Justification for the (common) denigration of "," is dubious. See the link in my answer.) — philipxy, Jan 31 '16 at 01:01

score 1 · Answer 1 · answered Jan 27 '16 at 02:09

An INNER JOIN clause has the potential, based on the results of its ON clause, to suppress records from the result set.

FROM a INNER JOIN b ON a.colx = b.coly

has precisely the same meaning as

FROM a, b WHERE a.colx = b.coly

and either of them will omit rows that fail the ON or WHERE clause.

If you were working in old-timey 1990s Oracle, you could write this so-called omega join

FROM a, b WHERE a.colx = b.coly(+)

instead of

FROM a LEFT JOIN b ON a.colx = b.coly

Either way you'll get every row from a in that result set, even the ones that don't match rows in b via the ON or WHERE clause.

The basic structure of a non-aggregating MySQL SELECT statement is this:

 SELECT a bunch of columns
   FROM a bunch of tables joined together
  WHERE a bunch of filtering predicates
  ORDER BY a bunch of columns
  LIMIT start,count

The stuff in the FROM clause includes all the JOINed tables. The stuff in the SELECT clause chooses the columns to include in the result set. There is absolutely nothing that says you can't mention a table in the FROM clause but leave out all its columns from the WHERE clause.

score 0 · Answer 2 · edited May 23 '17 at 11:59

The joins, either those expressed by comma (",") or "explicit joins" using keyword "JOIN", plus any ONs, are all part of the FROM clause.

Comma gives the same result as CROSS JOIN. Comma just binds more weakly than joins that use "JOIN". In MySQL, (INNER) JOIN can be written without an ON, in which case it means CROSS JOIN.

See also this (product-independent) answer re these various inner joins & a further link re OUTER JOINs.

See MySQL documentation re SELECT & JOINs.

MySql - Where do you need to list the tables from which to extract data from?

2 Answers2