difference between foreign key & Join

[Bandita Patel]

When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?

[Satya Maheshwari]

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]

ya thats what i want to ask.. whats the difference then?

[Ireneusz Kordal]

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]

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]

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]

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:

SQL> -- Create a "lookup" table to list just a few values. SQL> CREATE TABLE Allowed_Numbers(X INT PRIMARY KEY); Table created. SQL> INSERT INTO Allowed_Numbers(X) VALUES(17); 1 row created. SQL> INSERT INTO Allowed_Numbers(X) VALUES(42); 1 row created. SQL> -- Create a table with a foreign key, to limit the data allowed in it. SQL> CREATE TABLE FK_Test 2 ( 3 Allowed_Numbers INT, 4 FOREIGN KEY(Allowed_Numbers) REFERENCES Allowed_Numbers 5 ); Table created. SQL> -- If the number does not exist in the parent table, it will result in an error. SQL> INSERT INTO FK_Test(Allowed_Numbers) VALUES(1); INSERT INTO FK_Test(Allowed_Numbers) VALUES(1) * ERROR at line 1: ORA-02291: integrity constraint (BTKATCH2.SYS_C0036092) violated - parent key not found SQL> -- A number that does exist in the parent table is allowed. SQL> INSERT INTO FK_Test(Allowed_Numbers) VALUES(42); 1 row created. SQL> -- NULL is allowed even though it does not exist in the parent table. Foreign Keys do not exclude nulls. SQL> INSERT INTO FK_Test(Allowed_Numbers) VALUES(NULL); 1 row created. SQL> -- Let's just cleanup that mess. SQL> DROP TABLE FK_Test; Table dropped. SQL> DROP TABLE Allowed_Numbers; Table dropped.
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:

SELECT A.Id, B.Id FROM A, B;
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]

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]

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".