• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

difference between foreign key & Join

 
Bandita Patel
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?
 
Satya Maheshwari
Ranch Hand
Posts: 368
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
bandita rani wrote:When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?

Foreign keys are used for joining.
 
Bandita Patel
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya thats what i want to ask.. whats the difference then?
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bandita Acharya wrote:When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?


Foreign key primary role is to enforcing referential integrity between data in two tables, not joining.
Here you find the basics: http://en.wikipedia.org/wiki/Foreign_key
 
Thomas Hauck
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following page discusses the concept of a foreign key.

Define Foreign Key

The following page discusses the concept of an inner join.

SQL Inner Join

 
Tim Holloway
Saloon Keeper
Pie
Posts: 18277
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Keys aren't actually part of the abstract SQL concept. They're one of the underlays to the implementation to obtain better performance and data integrity.

You can join on just about anything, but if the column(s) you are joining on constitute a schema-defined Foreign Key, you obtain those benefits.
 
Brian Tkatch
Bartender
Pie
Posts: 516
24
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are some good answers here, so most of my answer will simply repeat them. Nonetheless, i do not think it is stated clearly.

Foreign Keys and joins having absolutely nothing to do with each other. Zero, nothing. Foreign Keys are technically not even SQL (concepts), they are part of the database concept of Relational Integrity, often referred to as RI. Let me explain.

When creating a table, you often want to constrain the data allowed in a column. This comes in three main flavors: Data Type, Validation, and Existence. The Data type must be defined directly after the column name (unless it is implicitly defined by a Foreign Key), Validations are optional Check and Unique Constraints (and Indexes), and Existence comes (when direct) via Foreign Keys. There are variations, but this is the main form of checking the data is what you expect and allow it to be.

A Foreign Key works by making sure the datum (when not null) exists in another table. For example:


A join is a SQL concept to describe the relationship between multiple tables in the FROM clause of a SQL statement. ANSI SQL also uses it as a SQL keyword. For example:


That is a basic "Cartesian" join. No Foreign Keys required.

--

As it so happen, there is a high correlation between tables that have foreign keys and table you will want to join, so much so, that tools that help you write queries will offer to join tables for you based on those joins, and that will often be exactly what you want. Regardless, please make sure you understand the difference between the two concepts, and repeat the mantra: RI and joins are not the same thing!

--

OK, once we're here, it's time for a SQL joke: A query walked into a bar and walking over to two tables asked, "May i join you?"
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic