Code Snippets: Left and Right Joins in WHERE statment of SQL Query

SQL Queries can often be written in multiple ways to obtain the same end result set. Tables for instance can be joined in the FROM clause or the WHERE clause of a SQL Statement. In the FROM Clause we can utilize LEFT, RIGHT, INNER keywords to denote if both sides of the statement needs to be present to return the results or if only 1 side needs to be.  These same joins can be accomplished in the WHERE clause by use of the *= and =*.  Where *= is equivalent to LEFT JOIN and =* is the same as RIGHT JOIN.

Example FROM clause LEFT JOIN

FROM User u 
  LEFT JOIN Role r on u.ROLE_ID = r.ID

Example WHERE clause LEFT JOIN

FROM User u,
  Role r
WHERE u.ROLE_ID *= r.ID
// Code Snippets // T-SQL //

Comments & Questions

Add Your Comment