Is Natural (Join) Considered Harmful in Production Environment

Is NATURAL (JOIN) considered harmful in production environment?

NATURAL JOIN syntax is anti-pattern:

• The purpose of the query is less obvious;
  
  • the columns used by the application is not clear
  • the columns used can change "unexpectedly"
• The syntax goes against the modularity rule, about using strict typing whenever possible. Explicit is almost universally better.

Because of this, I don't recommend the syntax in any environment.

I also don't recommend mixing syntax (IE: using both NATURAL JOIN and explicit INNER/OUTER JOIN syntax) - keep a consistent codebase format.

Syntax Error when trying to make a comparison with the same relation generated by a natural join

First note that natural joins are considered harmful: Is NATURAL (JOIN) considered harmful in production environment?. It is better to write an explicit inner join.

Returning to your question. You need to refer to individual tables from the natural join. For example:

SELECT
  A1.first_name, A1.last_name, AN1.phone_number
FROM
  Author A1 NATURAL JOIN AuthorNumber AN1,
  Author A2 NATURAL JOIN AuthorNumber AN2
WHERE
  AN1.phone_number = AN2.phone_number AND
  NOT (A1.first_name = A2.first_name AND A1.last_name = A2.last_name);

Demo: http://sqlfiddle.com/#!9/ba2951/7.

MySQL foreign key/natural join

From the documentation:

The NATURAL [LEFT] JOIN of two tables is defined to be semantically
equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that
names all columns that exist in both tables.

Both tables have a column name which is included in the natural join, making it fail for all combinations except for those where the artist and album names are the same (which I guess could happen).

You could use a join FROM Artists JOIN albums USING (ar_id) instead.

joining multiple tables in an SQL query

Left join 101... and you really....really need to read up on joins if you plan to work in RDMS.

select
    b.isbn
    ,b.title
    ,b.bookprice
    ,b.stock
    ,a.authorname
    ,o.ordernumber
    ,o.numcopies
    ,o.price
from
    book b
    inner join 
        BookAuthor ba on
        ba.isbn = b.isbn
    inner join
        Author a on
        a.authorid = ba.authorid
    left join
        orderline o on
        o.isbn = b.isbn
    left join
        bookorder bo on
        bo.ordernumber = o.ordernumber
where
    b.isbn = 1491936169

database select

select
  film.filmname
from
  film
  join film_category
  on film.film_id = film_category.film_id
where
  film_category.category_id = 3

Further info.

Is using JOINs to avoid numerical IDs a bad thing?

Primary key values should not be coded as literals in queries.

The reasons are:

• Relational theory says that PKs should not convey any meaning. Not even a specific identity. They should be strictly row identifiers and not relied upon to be a specific value
• Due to operational reasons, PKs are often different in different environments (like dev, qa and prod), even for "lookup" tables

For these reasons, coding literal IDs in queries is brittle.

Coding data literals like 'OPENED' and 'ONHOLD' is GOOD practice, because these values are going to be consistent across all servers and environments. If they do change, changing queries to be in sync will be part of the change script.

How to use COUNT on two tables with WHERE clause

You need to create a join on your sub-query to get the ticket status and the sub-query should look like below:

      SELECT COUNT(*) total
      FROM   tblTicket_Engineer
      INNER JOIN  tblTicketDetail ON tblTicketDetail.TicketID = tblTicket_Engineer.TicketID AND tblTicketDetail.Status = 1
      WHERE tblTicket_Engineer.EngineerID = 1

---

Related Topics