CODEWITHSUNDEEP
mysql
eMRe
eMRe

Reputation: 3247

Which query should i use?

I am trying to learn mysql properly. When i have a query using 2 tables, Do i need to write table names before the field name all the time?

This is the one i have got

SELECT 
owner.title, owner.forename, owner.surname,
pet.name, pet.breed
FROM
owner,pet
WHERE
owner.owner_id = pet.owner_id
ORDER BY
owner.surname ASC, pet.name ASC

if i do this way is it ok aswell?

SELECT 
title, forename, surname,
name, breed
FROM
owner,pet
WHERE
owner.owner_id = pet.owner_id
ORDER BY
surname ASC, name ASC

Upvotes: 2

Views: 102

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

Since 1992, this:

FROM
  owner,pet
WHERE
  owner.owner_id = pet.owner_id

is (by SQL-92 standards) written as:

FROM
    owner
  JOIN                         --- or the equivalent: INNER JOIN
    pet
      ON owner.owner_id = pet.owner_id

To answer your question, I would prefer the first choice (modified as above) over the second, mainly for readibility. Someone else may have to read your code (or you, after a few days or months) and knowing which table every column belongs can be useful:

SELECT 
  owner.title, owner.forename, owner.surname,
  pet.name, pet.breed
FROM
    owner
  JOIN
    pet
      ON owner.owner_id = pet.owner_id
ORDER BY
  owner.surname ASC, pet.name ASC

Another reason is to catch the case you (or someone) later adds (or renames) columns on the tables and you have a "name clash" which will make your query either give error and stop working or give different than expected results.

You could also use aliases, like this:

SELECT 
  o.title, o.forename, o.surname,
  p.name, p.breed
FROM
    owner AS o
  JOIN
    pet AS p
      ON o.owner_id = p.owner_id
ORDER BY
  o.surname ASC, p.name ASC

Upvotes: 3

WWW
WWW

Reputation: 9860

Best form in my opinion would be something like this (and remember, form is largely subjective):

SELECT o.title, o.forename, o.surname, p.name, p.breed
FROM pet p 
INNER JOIN owner o ON o.owner_id = p.owner_id
ORDER BY o.surname ASC, p.name ASC

I find queries like this the most readable and easiest to modify. If I have multiple tables in a query, I always alias my tables and I always use that alias in the field selection. The kind of query I wrote is also a fairly portable query, meaning it uses pretty basic SQL features and syntax, so you could take it to another DBMS and you probably wouldn't have to change anything.

Upvotes: 4

Vikas Naranje
Vikas Naranje

Reputation: 2402

If both table contain the same column name then you need to specify the table name with column name to prevent ambiguous situation, other wise it's not needed.

Upvotes: 0

halfer
halfer

Reputation: 20439

You don't have to use table names. But if you are accessing multiple tables, there is a likelihood of column clashes - in which case it is helpful to specify which table you mean.

Some db systems (notably PostgreSQL) will complain if it isn't sure which column you are referring to.

Upvotes: 2

Related Questions