CODEWITHSUNDEEP
mysqljoin
Robert
Robert

Reputation: 10390

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

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.

Upvotes: 1

Views: 33

Answers (2)

philipxy
philipxy

Reputation: 15156

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.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108816

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.

Upvotes: 1

Related Questions