November 19, 2011 at 5:56 pm
Hello, I have searched far wide on two topics, with no luck.
The two topics are
a) Relationships
b) Joins (Inner joins and cross joins etc)
I understand that creating a join, creates a link between two tables,
based on a condition.
Q. If a inner join connects two tables and a relationship connects
two tables, what is the difference between the a join and a relationship.
ANd How do they differ?
Q. Does anyone know of any SQL programming books that explain the difference between
relationships and joins in a clear fashion.
Q. If you have a join, why do you bother with relationships
Q. If you have a relationship, why do you bother with joins.
eg. Is it with relationships, a change in one table, will create a
change in the other table?
Q. I suspect a 'relationship' between the two tables, keeps the tables
in synch whilst minimizing duplicate data in the tables.
Whereas joins are not worry about duplicate data.
Q. So If you have a join, do you still need relationships?
Q. Therefore If you have a relationship, do you still need joins?
Q. Instead of a JOIN searching across two tables, can you just create
a WHERE clause against one of the tables that is defined in a relationship?
For example, if a relationship ties two tables together, does an INSERT
SQL action, on the first table, update the second table automatically.
Q. And finally can you use an INSERT action on a JOIN?
(Sorry I dont have a computer I can test these problems on)
------------------------------------------------------------------
The text below is a check to see If I have got the theory right
------------------------------------------------------------------
Lets see if I have got this right?
If there is duplicate data, you have to create a relationship.
to break down the base table in to two or more tables.
Establishing a relationship between two tables, removes duplication
but still keeps the data relationship between two tables, intact.
Which would lead to - If you have a change in one table, how is the
other table 'to know' what the changes (in the other table) are?
I suspect that the 'relationship' between two tables,
keeps the tables in synch. but as a rule, I have been told,
with relationships you cannot search between multiple tables.
You need a join to do a search across multiple tables.
THis leads to the question
Q. IF two tables are defined by a relationship with a primary key in the first
and foriegn keys in the second table. Do you or must you specify the
primaryid field or foriegn key, as the ON condition in a inner join?
Many thanks
Greg
November 20, 2011 at 5:58 am
ggpevans (11/19/2011)
Hello, I have searched far wide on two topics, with no luck.The two topics are
a) Relationships
b) Joins (Inner joins and cross joins etc)
I understand that creating a join, creates a link between two tables,
based on a condition.
Q. If a inner join connects two tables and a relationship connects
two tables, what is the difference between the a join and a relationship.
ANd How do they differ?
A join can be accomplished between two tables using any columns at all. They don't even have to have the same data type (although the data types have to be convertible to one another) although they should always be as a best practice. I don't usually use the term relationship when talking about physical data structures. Instead I talk about foreign key constraints (fk for short). A FK is an enforced referential constraint that ensures that the data in a child table matches the data in a parent table. This is done through one or more columns being defined as a primary key in the parent table and then those same columns are in the child table so that they can act as a link, a relationship, between the two tables. The FK enforces the relationship.
Q. Does anyone know of any SQL programming books that explain the difference between
relationships and joins in a clear fashion.
Since it sounds like you're just learning TSQL, I always recommend the same book, Itzik Ben-Gan's TSQL Fundamentals. It teaches you how to write queries the right way.
Q. If you have a join, why do you bother with relationships
The difference is a coding construct, a JOIN, and a data integrity enforcement construct, the FK. You need both. The FK is not automatically placed into code, and the code doesn't have to have the FK to work (although having an enforced FK is actually a performance enhancer)
Q. If you have a relationship, why do you bother with joins.
see above
eg. Is it with relationships, a change in one table, will create a
change in the other table?
Q. I suspect a 'relationship' between the two tables, keeps the tables
in synch whilst minimizing duplicate data in the tables.
Whereas joins are not worry about duplicate data.
Q. So If you have a join, do you still need relationships?
Q. Therefore If you have a relationship, do you still need joins?
All answered above
Q. Instead of a JOIN searching across two tables, can you just create
a WHERE clause against one of the tables that is defined in a relationship?
It is possible to put JOIN criteria into a WHERE clause instead of in the ON clause of the JOIN, but as a best practice, it's not a good idea.
For example, if a relationship ties two tables together, does an INSERT
SQL action, on the first table, update the second table automatically.
Q. And finally can you use an INSERT action on a JOIN?
Yes & no. You can only INSERT to a single table at once. But you can INSERT using a SELECT and the SELECT can use JOINs.
(Sorry I dont have a computer I can test these problems on)
------------------------------------------------------------------
The text below is a check to see If I have got the theory right
------------------------------------------------------------------
Lets see if I have got this right?
If there is duplicate data, you have to create a relationship.
to break down the base table in to two or more tables.
Establishing a relationship between two tables, removes duplication
but still keeps the data relationship between two tables, intact.
Which would lead to - If you have a change in one table, how is the
other table 'to know' what the changes (in the other table) are?
Not what the changes are. It's the primary key, the identifying field or fields, that is used to establish the relationship. If that field changes, it has to be changed in both tables. But, you actually can't change those fields because of the foreign key constraint (that's the whole idea) so most of the time, you want your primary key to be a field that does not change, ever.
I suspect that the 'relationship' between two tables,
keeps the tables in synch. but as a rule, I have been told,
with relationships you cannot search between multiple tables.
You need a join to do a search across multiple tables.
Not quite true. You JOIN between the tables and then you can search across them too.
THis leads to the question
Q. IF two tables are defined by a relationship with a primary key in the first
and foriegn keys in the second table. Do you or must you specify the
primaryid field or foriegn key, as the ON condition in a inner join?
Many thanks
Greg
The way it works is like this I have a table:
CREATE TABLE [dbo].[LocationType] (
[LocationTypeId] [smallint] IDENTITY(1, 1)
NOT NULL,
[LocationTypeDesc] [nvarchar](100) NOT NULL,
CONSTRAINT [LocationTypePK] PRIMARY KEY CLUSTERED ([LocationTypeId] ASC)
)
Then I have another table:
CREATE TABLE [dbo].[Location] (
[LocationId] [int] IDENTITY(1, 1)
NOT NULL,
[LocationTypeId] [smallint] NOT NULL,
[LocationName] [nvarchar](50) NOT NULL,
[LocationDetails] [nvarchar](MAX) NULL,
CONSTRAINT [LocationPK] PRIMARY KEY CLUSTERED ([LocationId] ASC)
)
The general idea is that I have a Location Type and I have Locations. Each Location can only be of one Type. I don't want to key in the Type over and over and get misspellings, mistakes, extra fields, whatever, so I'm going to use the look up table of LocationType to enforce the Types available in the Location table. I've created a primary key in the LocationType table, LocationTypeId, and I've got a matching column in my Location table, LocationTypeId. With these I'm going to define a foreign key referential constraint:
ALTER TABLE [dbo].[Location] WITH CHECK ADD
CONSTRAINT [Location_LocationTypeFK] FOREIGN KEY([LocationTypeId])
REFERENCES [dbo].[LocationType] ([LocationTypeId])
GO
And now, I can only add values to the LocationTypeId column in the Location table that match already existing values in the LocationType table. I can modify the description of a type all I want and it will never affect the Location table. But I can't change that LocationTypeID column because I've got an enforced constraint between those two tables that prevents.
Hopefully this clarifies things some. If you're really trying to learn TSQL and SQL Server, I'd suggest getting a computer and a copy of the Developer Edition of SQL Server ($50) or at least an Express Version (free) to try this stuff out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 20, 2011 at 3:16 pm
ggpevans (11/19/2011)
Hello, I have searched far wide on two topics, with no luck.The two topics are
a) Relationships
b) Joins (Inner joins and cross joins etc)
I understand that creating a join, creates a link between two tables,
based on a condition.
Q. If a inner join connects two tables and a relationship connects
two tables, what is the difference between the a join and a relationship.
ANd How do they differ?
I expect what you mean by "relationship" in this instance is really a foreign key constraint. Don't use the term "relationship" if you mean foreign key.
Assuming that a foreign key is what you mean then the answer to your question is straightforward. A foreign key constraint is a rule implemented in the database to prevent inconsistent data being created. A join is an operation that combines data from multiple tables in a query.
These are very basic questions. You will probably benefit from reading a non-SQL Server specific book that can introduce you to some fundamental database concepts. One good one is "Database Systems: The Complete Book" by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply