Sql-server – To Join two tables without using Primary key

sql server

Table1: Order;
Column Name: Id (PK), SecurutyId, UserId. ** But no foreign key in this table.

Table2: Security;
Column Name: Id (PK), SecurutyId (FK), Symbol.

Can I do That?

Select o.SecurityId, o.AccountId, s.Symbol
From Order as o, dbo.Security as s
Where s.Id= o.SecurityId ;

It is Giving me the result i want. Can anybody explain me why it is giving me the result? Because as far i know to join two tables we need one PK references to another table FK. Am i doing the right thing?

Best Answer

You can join on any column combination you like. You can even join on a condition that does not reference a column at all:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.TblA(a_id INT, c1 INT);
GO
INSERT INTO dbo.TblA(a_id,c1)
VALUES(1,345),(2,132);
GO
CREATE TABLE dbo.TblB(b_id INT, c2 INT);
GO
INSERT INTO dbo.TblB(b_id,c2)
VALUES(1,245),(2,456);
GO

Query 1:

DECLARE @some_value INT = 0;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

[no rows returned]

Query 2:

DECLARE @some_value INT = 2;

SELECT *
  FROM dbo.TblA AS A
  JOIN dbo.TblB AS B
    ON @some_value = 2;

Results:

| A_ID |  C1 | B_ID |  C2 |
|------|-----|------|-----|
|    1 | 345 |    1 | 245 |
|    1 | 345 |    2 | 456 |
|    2 | 132 |    1 | 245 |
|    2 | 132 |    2 | 456 |

In this example you get either the cartesian product of all rows in both tables, or no row at all, depending on the condition being met.

Now, if you have a foreign key declared, joining on those linked columns is called a natural join an that is the most common scenario for a join. But as you have seen, it is not the only possibility.

Independent of declared relational integrity, you want to make sure that your tables are indexed appropriately to support the (join) queries your application is made of. Adding an index on columns used in a join is usually a good idea (but there are exceptions).

For more information about SQL Server joins, check out my blog series from last december: A Join A Day

Related Question