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.

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.

Let's say I have 2 tables:

CREATE TABLE Cities (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    city tinyblob
);

CREATE TABLE Users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username TINYBLOB,
    city INT UNSIGNED,
    FOREIGN KEY (city) REFERENCES Cities (id)
);

If my application is to run an SQL query to get a user's profile data, how would I use a join to get the city associated with a user's record, and how would the outputted record appear?

share|improve this question
add comment

3 Answers

up vote 11 down vote accepted

Every result from a SQL operation is functionally a new table, whether or not it is stored on disk or in memory

The function of a join is to "Join" two tables together into a synthetic third table that (usually) only exists in memory during the time it is output to the application.

The reason to use a join is to reduce Data Anomalies, by insuring that data appears in one and only one place in the database.

Consider the following:

Person ( ID Integer Primary Key, FName String, LName String, Address String, City String, ZipCode String)

Here, whenever the government changes zip-codes (which happens entirely too frequently for database developers' tastes * the persons table will need to be updated such that all old zipcodes correctly belong to the correct city and new zip-code.

In this instance, the same zip code is in multiple tuples of the table, and if the table is editable manually, will be subject to severe update anomalies as new city/zip-code combinations are entered without the database as a whole being updated.

By having three tables instead, we get the same data, but with increased flexibility and reliability (at the cost of some performance, though questions of "appropriate denormalization" are a topic for another series of answers.)

Person ( PersonID Integer Primary Key, FName String, LName String)

City ( CityID Integer Primary Key, City String, State String, ZipCode String )

Address ( AddressID Integer Primary Key, AddressLine1 String, CityID Integer References City, PersonID Integer References Person)`

And we can get a single "table" out of the above through the following query: SELECT Fname, Lname, AddressLine1, City, State, ZipCode FROM Person INNER JOIN Address USING (PersonID) INNER JOIN City USING (CityID)

This allows us to update the individual tables whenever something specific about them changes, without corrupting the rest of the associated data.

An INNER JOIN is the default type of join. Roughly speaking it notes: "combine tuples where this record from table a matches this record from table b" The other types of joins are OUTER JOINS and what's known as a CARTESIAN PRODUCT. An outer join is "Take everything from the left or right table (as specified) and where the other table matches, join it. Where it doesn't match, fill it with nulls" And a cartesian product (without getting into the relational algebra is: Take every possible combination of tuples in both tables and output them into one table, without matching anything.

* More than never, especially in the case of moving the boundaries of zipcodes because of gerrymandering

share|improve this answer
add comment

I think you're looking for

SELECT username, cities.city FROM Users JOIN Cities ON (Cities.Id = Users.City)

which would give you

| username | city      |
| John     | Singapore |
| Toby     | London    |
| Eric     | Paris     |

but don't take my word for it: try it out on your database and see what you get!

share|improve this answer
add comment

In a nutshell, your SQL statement will be -

SELECT FIELD1, FIELD2 .. FIELDN
FROM PARENT_TABLE 
  INNER JOIN CHILD_TABLE ON ( PARENT_TABLE.PARENT_KEY = CHILD_TABLE.FOREIGN_KEY)

Fields1.. Fieldsn will be the fields that you would want to fetch - from either or both tabls.

share|improve this answer
    
Parenthesis are not mandatory, it makes the query harder to read. –  Spredzy Jan 4 '11 at 7:30
1  
@Spredzy Reverse's the case for me, Parenthesis increases the readability for me –  Sathya Jan 4 '11 at 7:32
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.