SQL join vs Primary key?

I an very new to Databases
Currently studying Access, SQL and using Coldfusion

I was reading in SQL you can use FROM and WHERE to JOIN two tables in a query. Where does the Primary key come in? Or is it even needed based on what you are using? Does the primary key need to be assigned on the tables in order to use that function?

I am confused, please help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
Each tabele SHOULD have a PK, for the purpose of enforcing uniqueness among the rows.

You can JOIN two tables in a query without using the PK, as long as the datatypes are the same (and the fields being joined make sense...)

Many very large systems have no relationships (not the same thing as JOINs) defined at all, and handle all that in the application...

Clear as mud?
silver00x00Author Commented:

So in a very large system that you speak of... What does it use to create a "relation" ?
Kevin HillSr. SQL Server DBACommented:
A relationship is a defined link between tables....many of them don't.  They just use a lot of error handling to ensure they aren't adding child rows without a parent.  Not my preference, but it does work.
Kevin3NF, you are right in all that you said, but you are kind of mixing PK and FK in here and I'm not sure it's clear.
PK = Primary Key, which is one or more columns that can uniquely define a row in a certain table.
FK = Foreign Key, which is a constraint that means any value in a certain column in my table has to exist in another table in order for me to be able to insert it.

Now, basically you don't HAVE to have any Keys(called Constraints)  at all. but they do make your job easier, they enforce the data integrity for you and save you all the trouble.

As to your question directly, the Primary key has no real affect on the query you make(your join) other than the fact it makes it more efficient(run faster). It will not limit you in any way and many systems that were created before the PK and FK were invented(not that many years ago...) still live without them, it just adds them alot of code lines making sure that no bad values are entered into the Database.
In most modern DBMSs, the primary key is the default clustered index on the table, therefore by default, records are physically and logically sorted according to their primary key, so it's often advantageous to join on the primary key first, then filter unwanted rows between large tables, since the join will be faster.  You can join on anything you want, but choosing a set of columns that doesn't have some sort of index  on it can create large performance problems since the DBMS will essentially either create an index on the column dynamically (sort of how a hash join works) or compare each row's columns in the first table to the join columns in the other table, one row at a time, which can take n*m time, and is extremely inefficient on large tables (nested loop joins).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.