Nested Loops Join

re: Nested Loops Join

bartd — Thu, 10 Aug 2006 00:47:37 GMT

Great info, Craig -- I don't think I've seen this common operator dissected so thoroughly anywhere else. Thanks!

re: Nested Loops Join

robineast — Mon, 14 Aug 2006 11:26:50 GMT

The section 'What about full outer joins' was fascinating. Can you expand on what that query plan is showing. I presume the Concatenation operator implements the Union with lines 2-4 being the first query and lines 5-9 being the second query. What's the Compute Scalar for? and the TOP(Expression)?

Also you mention in the note about the query plan using a clustered index scan instead of a seek. There are 2 clustered index scans in the plan and I presume you mean the first one - it wouldn't make sense to have an index seek for the outer table (or set) of an NL join.

re: Nested Loops Join

craigfr — Tue, 15 Aug 2006 02:18:29 GMT

Your analysis is correct. The concatenation is the union, the red lines (2-4) are the left outer join, and the green lines (5-9) are the semi-join to complete the full outer join.

Compute scalar simply computes an expression. In this case it computes NULL constants for the customer columns. This is needed since semi-joins do not produce output values for the inner table (the customer table in this case). Recall that the semi-join is just checking for existence; it does not actually match any particular row.

The top is an unnecessary optimization to stop the table scan from producing more than one row. Again, the semi-join is just checking for existence so a single match is all that we need. I say "unnecessary" because the semi-join would stop after a single match even without the top.

You are also correct, that the clustered index scan that I referenced is the inner one. Sorry for the ambiguity.

Joins Dissected on CraigFr's blog

Bart Duncan's SQL Weblog — Wed, 16 Aug 2006 22:29:36 GMT

CraigFr has a great series of posts in his blog describing the difference between the various logical...

Merge Join

Craig Freedman's WebLog — Thu, 17 Aug 2006 06:15:18 GMT

In this post, I’ll describe the second physical join operator: merge join (MJ).&nbsp; Unlike the nested...

Database Programming: Everything You Always Wanted To Know About JOIN Semantics, But Were Afraid To Ask

Ward Pond's SQL Server blog — Wed, 13 Sep 2006 02:34:56 GMT

Every once in awhile, I get an opportunity to look around for new and interesting things to read.&nbsp;...

Deadlock Troubleshooting, Part 3

Bart Duncan's SQL Weblog — Mon, 25 Sep 2006 21:39:27 GMT

Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock:...

Deadlock Troubleshooting, Part 3

Bart Duncan's SQL Weblog — Fri, 03 Nov 2006 22:25:57 GMT

Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock

Parallel Nested Loops Join

Craig Freedman's WebLog — Thu, 09 Nov 2006 06:26:32 GMT

SQL Server parallelizes a nested loops join by distributing the outer rows (i.e., the rows from the first

Query Plans and Read Committed Isolation Level

Craig Freedman's WebLog — Wed, 02 May 2007 22:08:47 GMT

Last week I looked at how concurrent updates may cause a scan running at read committed isolation level

re: Nested Loops Join

Gustavo Ayala — Thu, 14 Jun 2007 17:45:05 GMT

I don't understand how SQL estimates row count for the output of inner loop joins.

For example I have a query where I got 7 estimated row count I'm joining with a high selectivity secondary index ( actually the index is not clustered but it's unique ).

In the execution plan then I got 7 estimatod row count before the join , 1 estimated row count from the index and the output is 14 estimated row count !!

How come the estimated row count gets doubled if I'm joining with 4 columns ( wich have an unique index and I see the index is used in the execution plan )

Since I've got a lot of joins the problem escalates until the estimated row count gets too high and unrealistic.

re: Nested Loops Join

craigfr — Wed, 04 Jul 2007 00:12:12 GMT

I'm not sure I understand your question. I would suggest posting your question if possible along with an example repro and query plan to the SQL Server Database Engine forum at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1.

Join a SQL Server "joins type" - for Newbie

SQL Server Transact-SQL (SSQA.net) — Tue, 07 Aug 2007 17:38:02 GMT

Since the beginning of learning SQL Server I'm pretty much confused with JOIN conditions that defines

SQL in the Wild » Blog Archive » Execution plan operations - joins

SQL in the Wild » Blog Archive » Execution plan operations - joins — Sun, 30 Dec 2007 22:59:24 GMT

PingBack from http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/

Ranking Functions: RANK, DENSE_RANK, and NTILE

Craig Freedman's SQL Server Blog — Tue, 01 Apr 2008 00:15:00 GMT

In my previous post , I discussed the ROW_NUMBER ranking function which was introduced in SQL Server

Conversion and Arithmetic Errors

Craig Freedman's SQL Server Blog — Tue, 29 Apr 2008 02:44:34 GMT

Let's take a look at a simple query: CREATE TABLE T1 (A INT, B CHAR(8)) INSERT T1 VALUES (0, '0') INSERT

Random Prefetching

Craig Freedman's SQL Server Blog — Wed, 08 Oct 2008 00:51:00 GMT

In my last post , I explained the importance of asynchronous I/O and described how SQL Server uses sequential

What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

Craig Freedman's SQL Server Blog — Fri, 31 Oct 2008 00:53:19 GMT

SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats - that