SQL ingres syntax

Question

Unfortunately, I don't have access to an Ingres database at the moment and I'm just wondering if the inner join syntax that applies in standard SQL also applies in Ingres? I'm also wondering about the equivalent to inner join.

For instance, are the following two SQL statements valid?

Statement 1:

SELECT a.Value1,
       a.Value2,
       b.Value3
FROM   Tabletype1 a, Tabletype2 b, Tabletype3 c
WHERE a.Value1 = b.Value4
AND   b.Tabletype3_Num = c.Tabletype3_Num
AND  p.Value5 = 'Randomvalue'
AND b.Value3 > 20
      AND (a.Tabletype1Format = 'Random' OR a.Tabletype1Format = 'Random1')

Statement 2:

SELECT a.Value1,
       a.Value2,
       b.Value3
FROM   Tabletype1 a
       INNER JOIN Tabletype2 b
            ON  a.Value1 = b.Value4
       INNER JOIN Tabletype3 c
            ON  b.Tabletype3_Num = c.Tabletype3_Num
WHERE  c.Value5 = 'Randomvalue'
       AND b.Value3 > 20
       AND (a.Tabletype1Format = 'Random' OR a.Tabletype1Format = 'Random1')

Please indent code as code. Type it in as you did, then highlight each fragment, then click on the 'indent' button, marked '`{}`'. — Jonathan Leffler, Jan 16 '11 at 21:46
In allusion to **goto is evil**, I wish there is **joining tables by comma is evil** — Michael Buen, Jan 17 '11 at 00:21

score 4 · Accepted Answer · answered Jan 17 '11 at 11:17

4

In response to the question the OP actually asked, Ingres absolutely, for sure, definitely does fully support BOTH forms of join specification, and in every case I have ever bothered to look at, it comes up with exactly the same query plan.

So my bottom line answer is do what you think is preferable in your situation. It will work fine.

answered Jan 17 '11 at 11:17

Roy

56
1

For reference here is a detailed guide to SQL syntax with ingres - it covers JOINs: http://www.r20.nl/TheSQLGuidetoIngres-AppendixA.pdf – n34_panda May 24 '14 at 08:48

score 3 · Answer 2 · edited May 23 '17 at 11:44

3

Both forms work fine in Ingres, although you would appreciate the SQL92 ANSI syntax is more preferable for

readability
clarity
to help the query optimizer know where the join really happens

This question is very similar to SQL Inner Join syntax

edited May 23 '17 at 11:44

Community

1
1

answered Jan 16 '11 at 21:24

RichardTheKiwi

105,798
26
196
262

By SQL92 ANSI syntax you mean, the inner join.... on.. syntax? Are you sure this standard is definitely the "standard" standard in ingres? – santdrog Jan 16 '11 at 21:26
It is the ANSI standard, which may not mean much to some, but it is a standard for clarity and predictability. As the linked question states, Ingres/Oracle developers may have a tendency for the non-ANSI syntax, but it is on its way out in general. – RichardTheKiwi Jan 16 '11 at 21:28
I am aware of the link- however, it does not confirm anything unfortunately. So you can confirm that the syntax in statement 2 is definitely compliant with default implementations of ingres? – santdrog Jan 16 '11 at 21:31
What i was thinking when i read that was that ingres developers "tend to use the first style" because that is the way it is done in ingres, no? – santdrog Jan 16 '11 at 21:35
AFAIK, Ingres has supported the SQL92 join syntax for the last 5 years at least (when I first encountered it) if not longer. – RichardTheKiwi Jan 16 '11 at 21:43
1

@Brian - "that is the way it is done in Ingres". Let me put it this way, Oracle and Ingres have been around for too long. As such, there is a mountain of PL/SQL or QUEL code that looks that way that other people have come along to support. Oracle only supported SQL92 fully by v9. There is so much code to come across using the old syntax that some people just do not migrate off it, nor feel like they need to. – RichardTheKiwi Jan 16 '11 at 21:45

SQL ingres syntax

2 Answers2