• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

difference between foreign key & Join

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 368
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ya thats what i want to ask.. whats the difference then?
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Saloon Keeper
Posts: 28764
211
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?"
 
Thomas Hauck
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table.
The purpose of the foreign key is to ensure referential integrity of the data.
More on this topic can be found on the following web page.

Foreign Key
 
Tim Holloway
Saloon Keeper
Posts: 28764
211
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Thomas Hauck wrote:A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table.
The purpose of the foreign key is to ensure referential integrity of the data.
More on this topic can be found on the following web page.

Foreign Key



Actually, no. I spent many years on a system where the foreign keys were compounded from multiple columns, not just "another column".
 
Ranch Hand
Posts: 82
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Foreign keys are the columns of a table that points to the primary key of another table.
They act as cross-reference between tables. The purpose of the foreign key is to ensure the referential
integrity of the data. In other words, only values that are supposed to appear in the database are permitted.


A JOINs is used to combine rows from two or more tables, based on a related column between them.
JOINs are used when you extract/query data from the DB by giving rules on how to select the data. Joins:

join

1. Joins are multiple types of like inner join, outer join, left outer join right outer join, etc.

2. Joins are used to join two tables to combine the result set.

Foreign Keys:

1. A foreign key is constraint which used for reference purposes in DBMS.

2. FK contains a reference to the primary key in another table.

3. Can’t used to join table.
 
reply
    Bookmark Topic Watch Topic
  • New Topic