0

i'm a newbiee to all these sql statements and i have a very littele knowledge on full functionality of joins concepts....

so i have a figure here to have a quick visualisation of the concepts

enter image description here

Here i have two tables Table-A and Table-B

i just need to know how to get these three colors individually.

like sql statement to get the contents only in the blue region but not in green region.

please help me out with these queries.

Thanks alot guys.....

Prateek Raj
  • 3,830
  • 5
  • 38
  • 50
  • 2
    You really should search to see if your question has already been answered. [This SO question](http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins) contains a link to this [very excellent explanation](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) complete with the overlapping circle diagram. – DOK Jan 07 '12 at 16:18
  • @DOK thats great man but the images in that site are not getting loaded..... – Prateek Raj Jan 07 '12 at 16:20
  • 1
    @PrateekRaj Not sure what the problem is... everything is loading fine for me – Adam Wagner Jan 07 '12 at 16:28
  • Check out this link: http://www.devx.com/dbzone/Article/17403/0/page/4 – a_horse_with_no_name Jan 07 '12 at 16:31

3 Answers3

2

INNER JOIN returns at least one match in each table if there are more matches more rows are returned.

LEFT JOIN returns all rows from the 'left' table (basically the one specified in the FROM statement) regardless of whether there is a match, where there is no match it returns NULL or similar.

RIGHT JOIN is the opposite of a LEFT JOIN so returns all the rows in the 'right' table (basically the one specified after the word JOIN and before ON) regardless of whether there is a match, where there is no match it returns NULL or similar.

FULL JOIN is like both a LEFT JOIN and RIGHT JOIN in which all the records from both tables are returned, it will show both matching and non matching records from both tables

Your diagram to me to be honest is confusing it's coloured circles not tables of data so I can't match it to the concept of joins however...

Lets say you have table a containing data about colours (id, name, rgb, hex) and table b containing people (id, name, age, favourite_colour). The favourite_colour field contains the id of a record (colour) in table a. You can say that table b has a many to one relationship with table b on the afformentioned field. Now to get (join) the favourite colour of all the people in table a we can do something like

SELECT a.*, b.* FROM a LEFT JOIN b ON b.id = a.favourite_colour;

Here if a person in table a does not have a favourite colour then NULL is returned, if you had used INNER JOIN you would only get records of people with a favourite colour, if you had user RIGHT JOIN you would have got all the colours regardless of whether the were anyone's favourite and if you had used FULL JOIN you see all the people with there matched favourite colour, all the people without a matching favourite colour and all the colours that aren't the favourite of anyone.

Hope this help :-)

Edit: Having read the link provided in DOK's comment on the question the circles make sense :)

T I
  • 9,562
  • 3
  • 26
  • 49
1

et blue and red be two tables

To retrieve green:

SELECT * FROM blue
INNER JOIN red
ON blue.name = red.name;

To retrieve Blue:

SELECT * FROM blue
LEFT OUTER JOIN red
ON blue.name = red.name
WHERE red.id IS null;

To retrieve Red:

SELECT * FROM blue
LEFT OUTER JOIN red
ON blue.name =red.name
WHERE blue.id IS null
DOK
  • 31,943
  • 7
  • 59
  • 92
divyabharathi
  • 7
  • 1
0

let blue and red be two tables

To retrieve green: select * from blue intersect select * from red;

To retrieve blue; select * from blue minus select * from red;

To retrieve Red; select * from red minus select * from blue;

divyabharathi
  • 7
  • 1