SQL Inner Join Syntax

SQL INNER JOIN syntax

Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it's in the older definitions, just wasn't particularly widely used before then).

The use of the JOIN syntax is strongly preferred as it separates the join logic from the filtering logic in the WHERE clause. Whilst the JOIN syntax is really syntactic sugar for inner joins it's strength lies with outer joins where the old * syntax can produce situations where it is impossible to unambiguously describe the join and the interpretation is implementation-dependent. The [LEFT | RIGHT] JOIN syntax avoids these pitfalls, and hence for consistency the use of the JOIN clause is preferable in all circumstances.

Note that neither of these two examples are Cartesian products. For that you'd use either

SELECT c.name, o.product  
FROM customer c, order o  
WHERE o.value = 150  

or

SELECT c.name, o.product  
FROM customer c  CROSS JOIN order o 
WHERE o.value = 150

SQL - INNER JOIN of multiple tables is throwing a syntax error

Multiple errors here

   SELECT
    i.name AS invitee_name,
    c.first_name AS child_first,
    c.last_name AS child_last,
    s.invite_status,
    c.avatar
  FROM
    Invites AS i
  INNER JOIN Tokens AS t
    ON t.token_id = i.token_id
  INNER JOIN Children AS c
    ON c.child_id = i.child_id
  INNER JOIN Invite_Statuses AS s
    ON s.status_id = i.status_id
  WHERE
    t.token = qme34jh

AWS Athena (Presto) - inner join syntax

I suspect it is the comma(,) after e

SELECT e.*
FROM 
edgar_revenue AS e
INNER JOIN xbrl AS x
ON x."cik" = e."cik"

Postgres SQL inner join syntax

FROM property  
  INNER JOIN (locality INNER JOIN amount ON locality.code = amount.code)
     ON (property.band = amount.band) AND (property.id = amount."UniqueId")

is the same as

FROM property  
  INNER JOIN amount ON property.band = amount.band AND property.id = amount."UniqueId"
  INNER JOIN locality ON locality.code = amount.code

When INNER JOINs only, you can re-order them as you want.

(Any specific reason to JOIN locality? You don't select any of its columns. Is it some kind of EXISTS, or do you want multiple rows returned if there are several matching rows in that table?)

Correct syntax for table name under Inner Join?

You need to alias tables and use those like in below example - but in this case you will need

... 
  ...
FROM
  `book-to-film-adaptations.movies.movies_metadata_relevant` t1
JOIN
  `book-to-film-adaptations.goodreads_books.goodreads_books_relevant_data` t2
ON
  t1.title = t2.title       

or if join columns have same name (like in your case) you can use below version

... 
  ...
FROM
  `book-to-film-adaptations.movies.movies_metadata_relevant` t1
JOIN
  `book-to-film-adaptations.goodreads_books.goodreads_books_relevant_data` t2
USING (title)

---

Related Topics