1
  1. when inner join, is it always the case the condition of equality values are the equality of the primary key in one table and foreign key in another table.
  2. when inner join two tables that have the composite primary key and composite foreign key, is it we always have to join the two table by the equality of each component of the primary key and foreign key values.
jiaqi
  • 11
  • 2

3 Answers3

2

  1. No. that's not always the case. But it is what we usually do, it is the normative pattern. (A SQL INNER JOIN operation does not require that the predicate be an equality comparison. And it's not necessary that a comparison be made on PRIMARY KEY and/or FOREIGN KEY columns.)

  2. Again, that's the normative pattern, but it's not a requirement of INNER JOIN. If the intent is to perform a join on the primary key / foreign key relationship, then yes, the predicates would be equality comparison on all of the component columns of the keys.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • "select all rows form both participating tables as long as there is a match between the columns". the meaning of 'there is a match between the columns' meaning what? the condition(predicate) should be based on the common column? – jiaqi Mar 02 '16 at 03:12
  • and the difference between the 'on' and where. why in the outer join select f.fid, fname ,cid from faculty f left outer join qualification q where f.fid=q.fid; -- there is an syntax error pointing the where clause. – jiaqi Mar 02 '16 at 03:14
  • MySQL requires an `ON` clause for an outer join. The problem isn't the `WHERE` clause. The problem is that the `ON` keyword is missing. You could do something like this: **`... f LEFT JOIN ... q ON 1 WHERE f.fid=q.fid`**. But since the predicate in the WHERE clause will only be satisfied by non-NULL values of q.fld, that effectively negates the "outerness" of the join, relegating that to be equivalent to an inner join. – spencer7593 Mar 02 '16 at 19:21
0
  1. You can inner join by two tables by any fields but ... you describe standard inner join for base and dependent tables. And it's performance issue to join by fields with indexes
  2. The answer is the same as for first question - you can inner join as you wish. It depends on your schema and what you wish while do join of two tables
alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
0

The answer is no to both your questions.

The ON clause of a JOIN operation may contain any expression that evaluates to a 1 or 0, or none.

For example, you can write this.

  FROM t1
  JOIN t2 ON INSTR(t1.name, t2.surname) > 0 AND t2.nationality = 'US'

or even more horrendous sorts of things.

Foreign keys help enforce constraints. And, under favorable circumstances the associated indexes accelerate the query. But JOIN clauses are not at all limited to using keys and indexes.

O. Jones
  • 103,626
  • 17
  • 118
  • 172