Question about Inner Joins

Question

Question about Inner Joins

scott.atkins

Mr or Mrs. 500

Points: 580
More actions
October 29, 2010 at 3:31 pm

#231056

Hi Guys,
Im trying to get my head around inner joins and at this precise moment in time i can figure it out,
Can you reference any Column within any table but within the the same database? or do they have to be linked via the Primary keys?
as much info would be appreciated...
im using adventureworks to learn this so if someone could give a brief example and comment it i would really appreciate it!!
Thanks in Advance guys.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Andreas Goldman Old Hand Points: 313 More actions · Answer 1

Hi there,

You can join columns from different tables no matter if they are defined or "linked" to each other as keys or not. Moreover, the tables do not have to reside in the same database.

In a properly designed database though the most common example of joins would be between a primary key and a foreign key or between two primary keys, so I normally the columns being joined are keys "linked", but there is no such restriction in the join condition itself.

Sure I could provide you with examples, is there anything in particular you are looking for?

/Andreas Goldman

Andreas Goldman

scott.atkins Mr or Mrs. 500 Points: 580 More actions · Answer 2

Hi Andreas,

Thanks for the information,

Well something small if possible so i can get my head around it, and then ill build upon it as time progresses.

So say or example i wanted to link Firstname from a Table called "Employees" for example and link it with EmployeeAddress from a table called "EmployeesAddress" and there both not Foreign nor primary keys then the above would return a value?

Thanks again

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

Basically, an inner join forces both tables to have the same value in the columns you're using in the join.

Table1

ColomnA

1

2

3

Table2

Column A

1

2

SELECT Table1.ColumnA

FROM Table1

INNER JOIN Table2 ON Table2.ColumnA = Table1.ColumnA

Will show only:

Column A

1

2

Why? Because there is no match on columnA between the two tables for the record with the value 3. An inner join skips the rows where there is no match.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

scott.atkins Mr or Mrs. 500 Points: 580 More actions · Answer 4

Thanks very much for the example,

Im going to be picking this up again tomorrow! if i have any problems or anything like that ill come back!

Hopefully i wont have any problems though!

Thank again Appreciate it!

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 5

You're welcome. Have a good weekend. 🙂

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

scott.atkins Mr or Mrs. 500 Points: 580 More actions · Answer 6

hi all unfortuantely im back 🙁

im trying to run this and return gender and department name but its not working? its returning a value of "Null"

Can someone please take a look at it and let me know if possible....

Use AdventureWorks

go

SELECT HumanResources.Employee.gender

FROM HumanResources.Employee

INNER JOIN HumanResources.Department ON HumanResources.Department.name = HumanResources.Employee.gender

Thanks.

Andreas Goldman Old Hand Points: 313 More actions · Answer 7

Hi Scott and welcome back 😉

Basically, what you're trying to do through your query is returning all employees having the same gender as a name of a department.

INNER JOINING two tables is more or less returning all records having the same values in the two columns involved in the join, in your case department name and gender of employee.

Andreas Goldman

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 8

as you can see, inner join has nothing to do with primary key or foreign keys.

It is just a comparison of one or more columns of a data collection with one or more columns of another collection (or the same collection for that matters), based on the content of these columns.

e.g. If you gender column contains the values 'M' and 'F' in your persons table, you would be able to join that with your department table if that contains rows with department name 'M' and 'F'.

Although this will work, it raises questions on object naming.

Because you are coding an inner join, it will only return rows where the columns of both objects are matching.

With "left [outer] join" it would return all rows of the first named object, and only values of the second object for the rows it finds matching values for the columns in the ON clause.

A "right [outer] join" is just the other way around of the left join, concerning the object position in the query.

A full outer join would return all rows of both objects, but nulls in all columns of rows with non-matching values in the on clause.

Still not sure .... just draw two overlapping circles.

Consider each circle as a collection of data.

The overlapping part is the inner join, the left part combined with the overlapping part is the left join, the right part with the overlapping part is the right join, and the full collection of the three parts is the full outer join.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

scott.atkins Mr or Mrs. 500 Points: 580 More actions · Answer 9

Hi,

Hope you both had a good weekend 🙂

Thanks for the replies, i kinda understand what your saying, i probably sound really stupid asking these basics questions but i would rather ask then go away and then sit there twiddling my thumbs,

Would one of you be so kind to provide me with a basic working of an inner join using adventure works, so i can copy and paste it in to SQL and just have a better grasp and understand on it by seeing the script work and how its getting the relevant information from etc and how.

If someone can do that i would appreciate it alot.

Thanks in Advance

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

Hi Scott

check out these articles below, maybe they will help.

http://www.databasejournal.com/features/mssql/article.php/3618061/MS-SQL-Joins---Part-1.htm

http://www.databasejournal.com/features/mssql/article.php/3623916/MS-SQL-Joins-Part-2.htm

http://www.databasejournal.com/features/mssql/article.php/3630381/MS-SQL-Joins-Part-3.htm

regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

Hi Scott,

You can see several examples of queries with inner joins and outer joins in the AdventureWorks database itself. Open the Views folder and take a look at the views already defined. Right-click on a view and script the view as ALTER to see the actual code.

Here is one example:

Use AdventureWorks

Go

Select e.EmployeeID

,c.Title

,c.FirstName

,c.MiddleName

,c.LastName

,c.Suffix

,e.Title As JobTitle

,c.Phone

,c.EmailAddress

,c.EmailPromotion

,a.AddressLine1

,a.AddressLine2

,a.City

,sp.Name As StateProvinceName

,a.PostalCode

,cr.Name As CountryRegionName

,c.AdditionalContactInfo

From HumanResources.Employee e

Inner Join Person.Contact c On c.ContactID = e.ContactID

Inner Join HumanResources.EmployeeAddress ea On e.EmployeeID = ea.EmployeeID

Inner Join Person.Address a On ea.AddressID = a.AddressID

Inner Join Person.StateProvince sp On sp.StateProvinceID = a.StateProvinceID

Inner Join Person.CountryRegion cr On cr.CountryRegionCode = sp.CountryRegionCode;

Go

In this query, we are relating the Employee to the Contact (Person.Contact) on the value in the ContactID columns in both table. If there is no row in the Contact table for an Employee - that row will not show up in our results.

You have to join tables on columns that contain the same values. Trying to join between an address column in one table to a name column in another table won't work.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 12

Just keep in mind you have all doc in you hands - well pc/server actually - if you have installed Books online.