SQL Optimization

Question

Is there a performance difference between these two queries. Apparently WHERE should be reserved for querying values on the table to be SQL 92 compliant, is there a performance reason?

SELECT
  Foo.FooId
  (
    SELECT
      Bar.BarId
    FROM
      Bar
    WHERE
      Bar.FooId = Foo.FooId
    FOR XML PATH('Bar'), TYPE 
  ) As 'Bar'
  FROM
    Foo
  FOR XML PATH('Foo'), TYPE 

and

SELECT
  Foo.FooId
  (
    SELECT
      Bar.BarId
    FROM
      Bar
    JOIN
      Foo
    ON
      Foo.FooId = Bar.FooId
    FOR XML PATH('Bar'), TYPE 
  ) As 'Bar'
  FROM
    Foo
  FOR XML PATH('Foo'), TYPE 

Along the same lines, is there a performance difference between these two queries?

SELECT
  Foo.FooId
FROM
  Foo
WHERE
  Foo.FooId IN
  (
    SELECT
      Bar.FooId
    FROM
      Bar
    WHERE 
      Bar.SomeField = 'SomeValue'
  )

And

SELECT
  Foo.FooId
FROM
  Foo
JOIN
  Bar
ON 
  Bar.FooId = Bar.BarId
WHERE
  Bar.SomeField = 'SomeValue'

Answer 1

Generally the rules on joining and performance is this: Do the INNER/OUTER/LEFT/RIGHT/CROSS JOIN first using the ON clause and then if needed use a WHERE clause to further filter the data. The reason this is more efficient is table size. When you are selecting from two tables and joining on the WHERE clause, you do table_size_1 * table_size_2 records and then the WHERE clause is applied to get the actual join. When using a JOIN, you actually limit the records first, thereby reducing the size of your temporary table. Most if not all DBMSes are optimized to handle the JOIN ON over the joining on WHERE.

I'm not sure what effect the XML data has on the query, but on the actual table joining, both of your second examples are better.