Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
The reason that you should be joining on a key is to increase the likelihood of an index being used. –  Kermit Oct 30 '13 at 17:53
3  
You can join anything to anything. Whether it does what you want, or even makes sense, is a separate matter. :) –  Jon Seigel Oct 30 '13 at 17:54
    
Foreign and primary keys are constraints used for data and referential integrity. See technet.microsoft.com/en-us/library/ms179610.aspx –  KenWilson Oct 30 '13 at 17:56
    
Often when you want to join on a non-primary key, it indicates that that was what should have been the primary key. :) –  JamesRyan Oct 30 '13 at 19:41
add comment

2 Answers

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

share|improve this answer
add comment

Why don't you simply try this,

SELECT ORDER.SECURITYID, ORDER.ACCOUNTID, SECURITY.SYMBOL FROM ORDER 
INNER JOIN SECURITY ON (ORDER.SECURITYID = SECURITY.ORDERID) 

Also if you want outer join, you can do the same way using outer join or using union. Union does not require any relationships between tables, but the rows must be same in both the tables.

SELECT * FROM ORDER
UNION [ALL]
SELECT * FROM SECURITY

I hope this is the required answer, as far as I understood.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.