کمک در مورد معنی و مفهمو انواع جوینها در جدول

[oliya24]

سلام و خسته نباشید
5 تا دستور برای جوین وجود داره که من تو دوتاش مشکل دارم یعنی معنیش رو نمیفهمم
right outer join :کلیه رکوردهای جدول دوم و رکوردهای از جدول اول را که با جدول اول سازگارند را بازیابی میکند
left outer join هم به همین منوال من نمیتونم این دو رو درک کنم برخی از رفرنسای دیگه هم که نگاه کردم اینطور نوشتن
right outer join :این دستور ردیفهای خوانده شده از جدول شماره 2 یعنی جدول سمت راست برمیگرداند حتی اگر هیچ همخوانی با جدول شماره 1 یعنی جدول سمت چپ نداشته باشد


منظور از جدول سمت چپ و راست چیه؟؟؟؟ منظور از جدول 1 و 2 چیه؟؟؟
ممنون میشم من رو از ابهام در بیارید

[Galawij]

سلام،
این لینک (http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx) را مطالعه کنید متوجه می شید.

[oliya24]

اولا که لینک مشکل داشت دوما من فقط مفهوم این تعریف را خواستم نه بیشتر لطفا همین جا اگر میشه یه توضیحی بدین

[Galawij]

لینک مشکلی نداره، دقیقاً در پاسخ به سوال شماست همراه با تصویر که قابل فهم تر هست.
Introduction
In this article, we’ll see the basic concepts of SQL JOINs. In the later part of the article, we’ll focus on the advanced subject of Self-JOIN and some interesting observations on how inner JOIN can be simulated using left JOIN. The author has tried his best to amalgamate various topics in a single concept.
The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.
Inner JOIN

A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN. This is the default type of JOIN in the Query and View Designer.
http://dotnetslackers.com/images/articleimages/sqljoins1.jpg
Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You can create three different outer JOINs to specify the unmatched rows to be included:
Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.
http://dotnetslackers.com/images/articleimages/sqljoins2.jpg
Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.
http://dotnetslackers.com/images/articleimages/sqljoins3.jpg
Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.
http://dotnetslackers.com/images/articleimages/sqljoins4.jpg
Additional Notes related to JOIN

The following are three classic examples to demonstrate the cases where Outer JOIN is useful. You must have noticed several instances where developers write query as given below.
1.SELECT t1.*
2.FROM Table1 t1
3.WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
4.GO


The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.
1./* LEFT JOIN - WHERE NULL */
2.SELECT t1.*,t2.*
3.FROM Table1 t1
4.LEFT JOIN Table2 t2 ON t1.ID = t2.ID
5.WHERE t2.ID IS NULL


The above example can also be created using Right Outer JOIN.
http://dotnetslackers.com/images/articleimages/sqljoins7.jpg
NOT Inner JOIN
Remember, the term Not Inner JOIN does not exist in database terminology. However, when full Outer JOIN is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner JOIN. This JOIN will show all the results that were absent in Inner JOIN.
http://dotnetslackers.com/images/articleimages/sqljoins8.jpg
Cross JOIN

A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. One common example is when a company lists all its products in a pricing table to compare each product with others prices.
http://dotnetslackers.com/images/articleimages/sqljoins5.jpg
...

[nedata]

فرض مي كنيم 2 تا جدول داريم جدول T1و T2.جدول T1 داراي فيلد id,Family و جدول t2 شامل فيلد id,Name است.
right outer join:
وقتي join روي 2تا جدول مي نويسيم يك جدول را در سمت راست كلمه join و جدول ديگر را در سمت چپ مي نويسيم.وقتي از right outer join استفاده مي كنيم منظور اين است كه دو جدول را تركيب كند و تمام محتويات جدول سمت راست كه در جدول سمت چپ نيست را هم بياورد.مثال:در جدول t1:id=1 ,family=ahmadi;id=2,family=naderi و در جدول t2:id=1,Name=Ali;id=3,Name=Reza

Select t1.id,t1.family,t2.name From T1 right outer join T2 on t1.id=t2.id
خروجي بصورت زير است:
1,Ahmadi,Ali
Null,Null,Reza
و حالا اگر از left outer join استفاده كنيم

Select t1.id,t1.family,t2.name From t1 left outer join t2 on t1.id=t2.id
خروجي به صورت زيراست:
1,Ahmadi,Ali
2,Naderi,Null