Join (Select ... ) Ue on 1=1

JOIN (SELECT ... ) ue ON 1=1?

It's simply doing a cross join, which selects all rows from the first table and all rows from the second table and shows as cartesian product, i.e. with all possibilities.

JOIN (LEFT, INNER, RIGHT, etc.) statements normally require an 'ON ..." condition. Putting in 1=1 is like saying "1=1 is always true, do don't eliminate anything".

How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

SQL join with the same table with different select

You can use exists:

select t.*
from mytable t
where exists (
    select 1
    from mytable t1
    where 
        t1.col1 = t.col1 
        and t1.col2 = t.col2 
        and t1.col3 = t.col3 
        and t1.col4 >= 1000
)

Inner join and left join gives same results with where filter and hence which join to use?

This happens when you apply the filter (where clause condition) on the table you're left joining on. In this case the 'Order' table.

It is because your WHERE clause explicitly filters rows from the Order table where the color is pink. It will then join on only the matching Order rows on the Customer table.

You'll see that when you remove the where clause, the left join will function as you expect. :)

SQL How to join two query's with different columns and joins

Try LEFT OUTER JOIN:

SELECT t1.companyID,
       t2.normalWorkType,
       t1.contractnumber,
       t1.employeeNumber,
       coalesce(t2.value1,0) as t2.value1,
       t1.value2
FROM DatabaseName.t1 AS t1 LEFT OUTER JOIN
     DatabaseName.t2 AS t2 ON t1.employeenumber = t2.employeenumber  
       AND t1.companyID = t2.companyID

Use Left Join Alias in Column Select in SQL Views

I would try with APPLY :

SELECT p.Name, STUFF(ss.skills, 1, 2, '') AS Skill
FROM Persons p OUTER APPLY
     (SELECT ', ' + s.Name 
      FROM Skills s JOIN 
           PersonSkillLinks psl 
           ON s.Id = psl.SkillId 
      WHERE psl.personId = p.Id 
      FOR XML PATH ('')
     ) ss(skills);

By this way, optimizer will call STUFF() once not for all rows returned by outer query.

---

Related Topics