Transact-SQL Shorthand Join Syntax

Transact-SQL shorthand join syntax?

The =* and *= are not complaint with current SQL standards, I believe these operators will be deprecated soon you should always use the standard join syntax. The other operators that you mention are confusing and need to go away, I cringe when I see these in database objects.

T-SQL syntax abbreviation when JOIN columns have same name in both tables?

There is a natural join in the ANSI 92 SQL standard

SELECT … FROM T1 NATURAL JOIN T2

However, this is not supported in SQL Server. Probably a good thing as there could be ambiguities when you do not specify joined columns explicity.

There is no abbreviation like the syntax you mentioned

    FROM T1 JOIN T2 ON X;

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

SQL Server *= Operator?

Remove this code immediately and replace with a left join. This code does not always interpret correctly (Sometimes SQL Server decides it is a cross join) even in SQL Server 2000 and thus can give incorrect results! Also it is deprecated for the future (Using Outer Joins, SQL Server 2000 documentation archived from the original).

I'm going to add that in adjusting to left joins you should remove all of those other implicit joins as well. The implicit join syntax has been obsolete since 1992, there is no excuse for it still being in production code. And mixing implicit and explicit joins can give unexpected results.

---

What's the difference between comma separated joins and join on syntax in MySQL?

There is no difference at all.

First representation makes query more readable and makes it look very clear as to which join corresponds to which condition.

Difference between JOIN and INNER JOIN

They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

Alternative to *= for SQL Server 2012

Ok, I'll take a stab, although the *= syntax is not very obvious when it comes to rewriting them as a LEFT OUTER JOIN so you'll really want to test that it's equivalent;

...
FROM TRINVS T
INNER JOIN PARTYMST P      
  ON P.PARTY_ST + P.PARTY_NO = T.PARTY_ST + T.PARTY_NO
INNER JOIN IMTRANS I       
  ON I.DOC_TYPE + I.DOC_NO = T.DOC_TYPE + T.DOC_NO 
INNER JOIN IMTRANSA IA     
  ON IA.DOC_TYPE + IA.DOC_NO = I.DOC_TYPE + I.DOC_NO
LEFT OUTER JOIN TRINVSA TA 
  ON I.DOC_TYPE = TA.DOC_NO
WHERE T.DOC_TYPE = '   30' AND T.DOC_NO = 'byrod125378'
ORDER BY T.DOC_NO

As a sadly somewhat incomplete summary, what you want to start with to move the join conditions from the WHERE clause to the ON clause of a JOIN is;

• *= comparisons should be written as a LEFT OUTER JOIN with an = comparison in the ON clause.
• =* comparisons should be rewritten as RIGHT OUTER JOIN with an = comparison in the ON clause.
• = comparisons not involving a left- or right joined table should be rewritten as INNER JOIN with the comparison moved to the ON clause.
• = comparisons involving right- or left joined tables are normally moved to the corresponding LEFT OUTER JOIN or RIGHT OUTER JOIN ON clause, but this is really where the mapping is no longer straight forward and may need further analysis/testing to be equivalent.

MySQL JOIN vs MySQL Shorthand JOIN(,)

Always prefer the first option.

Both options are standard, but the second option is from the older SQL-89 standard. The first option is the new SQL-92 standard. The older standard is deprecated, and some databases are beginning to follow through on that deprecation. For example, Sql Server 2012 dropped support for doing out outer joins via the old syntax. It's still fully supported by MySQL, but you shouldn't really count on it staying that way for the life of your application.

Going beyond this, those are not the same queries. You need to add some parentheses to the WHERE clause to control how your OR condition is interpreted. This is why the queries return different sets of rows. As written, it's as if your where clauses look like this:

Method 1:

WHERE  (o.status = 'Paid' AND bc.userid = 451)
    OR (p.catid = 26 AND p.published = 1)

Method 2:

WHERE 
   (o.status = 'Paid'
      AND o.id = bc.order_id
      AND bc.userid = 451
      AND bc.plan_id = s.id
      AND bc.course_id = p.id
   ) OR ( p.catid = 26 AND p.published = 1)

Note the groupings... that's probably not what you intended. I expect you intended something more like this:

WHERE  o.status = 'Paid'
  AND  o.id = bc.order_id
  AND  bc.course_id = p.id
  AND  bc.plan_id = s.id
  AND (bc.userid = 451 OR p.catid = 26)
  AND  p.published = 1

Right now neither of your queries works this way, but I think it's closer to what you were really trying to do. You need the write the query with the parentheses in right places to get the results you want.

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

Oracle left-outer-join syntax shorthand notation (+) available in HQL?

Is there any way to specify a left outer join in HQL?

Yes, HQL does support left outer joins via explicit join syntax, and the syntax is identical to SQL's (with the implied navigation property as the join key):

from Cat as cat
    left join cat.kittens as kitten

outer is optional, viz left [outer] join

Source: HQL Query Reference

Is there a shorthand abbreviation for left outer join, like Oracle's (+)

No, AFAIK the HQL documentation makes no reference to a shorthand left outer join notation, and if HQL adheres to the trend in RDBMS to remove proprietary join syntax, as per @a_horse_with_no_name's comment, Oracle recommends against using the proprietary (+) syntax, and similarly *= in MSSql Server has been deprecated.

---

Related Topics