SQL Join question

[bdaedalus359]

I'm more or less a novice at SQL but, the company I work for uses it exclusively so, I'm trying to learn everything I can about it.

Something I've been confused about that the books I have been reading don't clearly state is how Joins, specifically Inner Joins, work with Keys. Basically, here's my question.

Do Inner Joins work by Joining two Primary Keys? Or Two Foreign Keys? Or is a Primary key to a Foreign key? Or even a Foreign key to a Primary Key?

I'm very confused about the whole thing because we use absolute massive databases with hundreds of tables here where I work but, I'm clearly not understanding the logic because sometimes my Joins work but most of the time they don't.

[Rubberman]

Please provide a sample schema and some examples of what you are trying to do, both descriptive and actual SQL code. Also, what books have you read to learn about the subject?

[bigtomrodney]

A primary key should join to a foreign key, a unique identifier should be a unique master record only once, that's your primary key. Any tables with information contain a foreign key to join back to the master data .

All of that aside, primary and foreign keys do not define an inner join. An inner join is one that returns only the data where there is a record and a related record on the second (or subsequent) table. An outer join contains records that do not have a related record, for example a left outer join contains all of the records on the table on the left regardless of whether there is a related entry on the table on the right.

[Cabhan]

So depending on which DB you're using, JOINs can vary a bit. But they basically work like this:

You might have two tables that relate to each other. For instance, suppose we have a database that contains college classes. You might have a table of semesters, which looks like:

* semesters
- semester_id: INT
- semester_name: VARCHAR

And then you might have a table of courses that looks like:

* courses
- course_id: INT
- course_name: VARCHAR
- professor: VARCHAR
- semester_id: INT

Now, of the many things you can do with these, you might want to select all of the courses, but also have access to the name of the semester that the course was in. This is where JOINs come in. A JOIN allows you to connect rows of different tables when they are related by something (almost always a foreign key). So for instance, I can do something like this:

SELECT c.course_name, s.semester_name FROM courses c JOIN semesters s ON c.semester_id = s.semester_id

This says that whenever you find a row in the courses table, find a row in the semesters table that has the same semester_id and treat these as a single row.

Now then, some DBs do some interesting things. For instance, I believe that MySQL has something called a "natural join", where if you join two tables where the first has a foreign key dependency on the second, it automatically joins on that foreign key <-> primary key connection. But I don't know much about that, and frankly, I've never used it.

I also simplified things a bit, because in MySQL at least, you can set a column as a foreign key. A foreign key column means that the value in the column MUST correspond to an existing primary key in the depended-upon table. Furthermore, you can also set the behaviour for when the row in the depended-upon table is deleted (for instance, we could say that the semester to which the course refers must exist, and if the semester is deleted, automatically delete any courses in that semester).

I hope that this clears something up. If you provide us an example of an actual problem you're having, it will be far easier to help.