SQL relationships, multiple methods, same result? Select, From, Where OR Select, From, Join, Where

Question

I've got 2 tables, a questions table and an answers table with the following example data:

+-----------------------------------+
| Questions                         |
+----+------------------------------+
| id | title                        |
+----+------------------------------+
|  1 | What is your favourite game? |
|  2 | What is your favourite food? |
+----+------------------------------+

+-------------------------------------------------+
| Answers                                         |
+----+------------------------------+-------------+
| id | text                         | question_id |
+----+------------------------------+-------------+
|  1 | The Last Of Us               |           1 |
|  2 | PlayerUnknowns Battlegrounds |           1 |
|  3 | Uncharted                    |           1 |
|  4 | KFC                          |           2 |
|  5 | Pizza                        |           2 |
+----+------------------------------+-------------+

Creating a one to many relationship as in one question can have many answers, I can do any of the following:

SELECT
    id, text
FROM
    answers
WHERE
    question_id = 1

Or:

SELECT
    answers.id, answers.text
FROM
    answers
JOIN
    questions
ON
    answers.question_id = questions.id
WHERE
    questions.id = 1

Or:

SELECT
    answers.id, answers.text
FROM
    questions
JOIN
    answers
ON
    questions.id = answers.question_id
WHERE
    questions.id = 1

Which all return the following (expected) results:

+-----------------------------------+
| Results                           |
+----+------------------------------+
| id | text                         |
+----+------------------------------+
|  1 | The Last Of Us               |
|  2 | PlayerUnknowns Battlegrounds |
|  3 | Uncharted                    |
+----+------------------------------+

Should any of them be avoided? Is there a preferred way of doing this? Just curious about the dos and don’ts of querying relationships in general really.

Possible duplicate of [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/questions/33947260/is-there-any-rule-of-thumb-to-construct-sql-query-from-a-human-readable-descript) — philipxy, Mar 14 '18 at 02:53
FKs (foreign keys) ("relationships" [sic] in pseudo-ER methods) are constraints & are not needed to query. *Tables* represent business/application relation(ship)s/associations. You build the query expression whose result holds the rows you want given what rows base tables hold. If constraints hold then some expressions that wouldn't otherwise always return the same result do. A FK says that when its column values participate in one particular relationship they also participate in a certain other one in a certain way. We just frequently join tables between which there are FKs. — philipxy, Mar 14 '18 at 02:54

score 1 · Accepted Answer · answered Mar 13 '18 at 14:30

1

If you only want to get the answers, don't involve the questions table. Just select from the answers.

Adding unused tables into your query makes no sense at all - It makes the query harder to read, thus harder to maintain, and It makes the database work harder (though modern databases might just optimize the unused parts of the query away) to get the same results.

answered Mar 13 '18 at 14:30

Zohar Peled

79,642
10
69
121

That's what I thought, but I've seen multiple articles that explain relationships and they all use JOIN in their statement, I just wasn't sure if I was overlooking it or if I'd missed the point of something along the way! – no. Mar 13 '18 at 14:34
relationships is not the same as joins. a relationship is enforced by a foreign key. A join is simply a way to select from multiple tables assuming some column(s) can be used to identify the connection between rows. – Zohar Peled Mar 13 '18 at 14:52
I understand the primary/foreign key aspect within the database but if I have an ORM take Laravels Eloquent ORM, you have a question model and an answer model, in the question model you can create an answer method and assign return a model object of answer with a hasMany method which retrieves all the answers to that question, is that simply returning just answers or does it retrieve a join of both tables with all the data from both? – no. Mar 13 '18 at 15:04
I don't know, it depends on how the ORM is implemented. – Zohar Peled Mar 13 '18 at 15:14
@Joe Please find a way to edit your question (without invalidating answers), or ask a new question, so that you ask what you mean to ask & elicit answers that tell you what you actually want to know. Edit clarifications into your post, not comments. – philipxy Mar 14 '18 at 02:44
Apologies, I’ve had my initial question answered by @ZoharPeled so I’ve marked it as the answer, I’ll look into other ORMs and see if I can get more information and see if a potential new follow up question is needed – no. Mar 14 '18 at 04:53

score 1 · Answer 2 · answered Mar 13 '18 at 14:35

1

If you want to imply relationship between "questions" and "answers" table then you can make id column from "questions" table as Primary key and question_id column from "answers" as Foreign key and you use JOIN when you need data(columns) from more than one table in your case if you want title column to be included then you can JOIN tables

answered Mar 13 '18 at 14:35

Sas

278
1
12

Sorry I probably should of mentioned my tables already have primary/foreign keys assigned, so the database itself is aware of the relationship. But I see what you mean, I'd only use JOIN when I want to get data from both tables, not just one. – no. Mar 13 '18 at 14:39

SQL relationships, multiple methods, same result? Select, From, Where OR Select, From, Join, Where

2 Answers2