• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

Enforce Join

I want to know the difference between the different types of enforce join:

-Not enforced
-Enforced From
-Enforced To
-Enforced Both

0
MKItani
Asked:
MKItani
  • 2
  • 2
  • 2
  • +2
6 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
 
MKItaniAuthor Commented:
i read these article before but it is not clear at all,
So please any body can send me a real example or an explanation more effective.
0
 
GhunaimaCommented:
For details & Example of Types of Joins in SQL server Go to
http://www.mssqltips.com/tip.asp?tip=1667
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
KrtyknmCommented:
Enforcing joins are used in SQL server to optimize the query, below link will help you to identify
http://msdn.microsoft.com/en-us/library/ms173815.aspx
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> i have choiced the false category.

Instead of deleting this question, you can request Moderators to add this question into Crystal Reports zone.
Now that I have objected, Moderators can add this question to appropriate zones.
0
 
MKItaniAuthor Commented:
Please remove this question form ms SQL SERVER CATEGORY to Crystal report category
0
 
mlmccCommented:
0
 
mlmccCommented:
A real example

Consider an Order system

Order Table
OrderId                CustomerId                   OrderDate                  ShipDate
   1                            1                               1 May 2011               4May 2011
   2                            3                               3 May 2011               6 May 2011
   3                            4                               8 May 2011
   4                            5                               11 May 2011

Order Detail Table
OrderDetailId      fkOrderId        ProductId     Quantity        
     1                           1                   P1                2
      2                           1                  P2                 1
     3                            2                  P1                 10
     4                            2                  P4                 1
      5                           3                  P2                 1
     6                            5                  P5                 2

Customer Table
CustomerId   Name            Address            City      
     1                J Smith          11 Some St      SunCity
     2                B Jones         12 Another Ave   NewCity
     3                K Doe             15  A St             SomeCity
     5                J Doe              20 A St              Some City

  Select Order.* FROM Order INNER JOIN OrderDetail ON Order.OrderId = OrderDetail.fkOrderId

Not Enforced - 4 records are returned   - No fields from Order Detail selected so it is ignored.  Same as
Select Order.* FROM Order

Enforced       - 3 records are returned   OrderIds 1,2,3  OrderId 4 is not returned because there is no matching record in the detail table.

Enforced From - 4 records returned.  Ignored since no fields from OrderDetail selected

Enforced To - 3 records returned same as Enforced

Another example
Select OrderDetail.* FROM Order INNER JOIN OrderDetail ON Order.OrderId = OrderDetail.fkOrderId

Not Enforced - 6 records returned.  Same as Select OrderDetail.* FROM OrderDetail

Enforced   - 5 records returned.   OrderDetail 1 - 5  All have match in Order table

Enforced From - Same as Enforced

Enforced To - Same as not enforced

mlmcc


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now