March 5, 2020 at 12:54 am
This is a weird query, can anyone please help me understand the following....
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(a.last_updated_date) <= TRUNC(sysdate - 13)
GROUP BY a.code, a.name
March 5, 2020 at 1:34 am
This is INNER JOIN. In distant past, SQL did not have JOIN keword, so people used WHERE block to specify both JOIN condition and filters. Your statement would look like this nowdays:
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a
INNER JOIN nmmaster b ON a.code = b.code -- same as WHERE a.code = b.code
WHERE a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(a.last_updated_date) <= TRUNC(sysdate - 13)
GROUP BY a.code, a.name
ON a.code = b.code means the same thing as WHERE a.code = b.code. Old syntax simply used WHERE to create connection between tables, and to state condition. New sintax (at least 20 years new 😉 separates table connection condition and filtering conditions. The new syntax is part of ANSII standard for SQL language.
Ome ORACLE developers often use the old syntax (WHERE). It works for INNER JOIN but not for OUTER LEFT/RIGHT.
🙂
Zidar's Theorem: The best code is no code at all...
March 5, 2020 at 1:36 am
This is an old style of "Equi-Join" that has been replaced by the current "Ansi Standard" joins that most of us use today. Yes, the join is being done in the WHERE clause. It used to be the "right way" to do it because it used to be the only way. The method has been deprecated for a long time in SQL Server and they've removed the ability to do the old style of outer joins. However...
What a lot of people don't understand though, is that this old style of join will never go away as long as correlated subqueries, WHERE EXISTS, and APPLY (to name a few) can be used.
Considering the use of TRUNC, I'm thinking this isn't T-SQL, though. It's also not going to be fast code because that bit of code is non-SARGable.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2020 at 11:41 am
who needs these new fangled inner and outer join types anyway? 🙂 they are so 1992 🙂
its 28 years since that standard came into play and i cannot understand why the oracle community can't embrace it.
and wallywizard, you asked all the right questions
answers
apologies, joins in the where clause make me extremely grumpy 🙂 lol
MVDBA
March 5, 2020 at 9:42 pm
>> What kind of join is this? <<
This is called an inner equi-join. It is the simplest possible join defined by Dr. Codd and should have been discussed during the first week that you talked about joined your SQL class.
>> This even the correct way of doing it? <<
Yes, this is very correct. This is the original syntax that came with the SQL standards. I've done a whole article somewhere can't which I can't find right now on the original syntax versus the infix join syntax that came much later. The way the select statement works is that you first go to the from clause, and construct a working table that is local to this statement. You then apply the constraints to this working table that you find in the where clause. This results that is then passed to the select clause and filtered to give the final result.
People who write with the original syntax are like mathematicians who know how to use capital Sigma notation for summations. They are thinking in terms of sets and aggregates. People who write in fixed joins are like people who use a string of plus signs to do a summation. Sometimes you have to use an in fixed operator, but not for inner joins.
The fact that you're asking such an elementary question and that the rest of your code shows me after 30 years that you don't quite get it yet. You don't know that things like "code", "name", and "status" are called attribute properties and are meaningless by themselves. They have to be some kind of code, some kind of status in the name of something in particular. This is a result of the most fundamental principle of logic called the law of identity (to be is to be something in particular; to be nothing in particular or everything in general, is to be nothing at all)
As an old-time programmer I also got a real laugh out of seeing someone the 21st century naming tables as if they were magnetic tape master files. As a complete violation design principle of not mixing data and metadata. You also don't seem to know the status uses a nominal scale and therefore cannot be a numeric value. I'm also trying to figure out why you think "a" and "b" are meaningful, useful names for aliases. The reason people use single letter names as aliases is so it will look like the lettered tape drive names. Basically, this is the way we programmed over 50 years ago.
But perhaps more than that,the idea of having a "code master" or a "name master" makes no sense in RDBMS. We would use DDL and REFERENCES clause for the codes or a "CHECK (foobar_code IN (..))" clause if the list is static and short
Would you explain what it means to truncate a temporal value? You also missed the point that we don't mix data and metadata, so information about updating a table is never stored in the table itself. This is the RDBMS equivalent of keeping a spare set of car keys in the car so that it fits stolen or damaged, you don't have an extra set of keys.
Based on what little I have to go on. I would guess that your code should have looked more like this skeleton:
CREATE ABLE Personnel
(emp_id CHAR(16) NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL,
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),
something_status CHAR(1) NOT NULL
foobar_code CHAR(3) NOT NULL
CHECK (foobar_code LIKE '[a-z][0-9][0-9]');
SELECT job_code, emp_name, COUNT(DISTINCT zip_code) AS zip_cnt
FROM Personnel
WHERE something_status = '1'
AND foobar_code <> 'a10'
GROUP BY job_code, emp_name;
>> What kind of join is this? <<
This is called an inner equi-join. It is the simplest possible join defined by Dr. Codd and should have been discussed during the first week that you talked about joined your SQL class.
>> This even the correct way of doing it? <<
Yes, this is very correct. This is the original syntax that came with the SQL standards. I've done a whole article somewhere can't which I can't find right now on the original syntax versus the infix join syntax that came much later. The way the select statement works is that you first go to the from clause, and construct a working table that is local to this statement. You then apply the constraints to this working table that you find in the where clause. This results that is then passed to the select clause and filtered to give the final result.
People who write with the original syntax are like mathematicians who know how to use capital Sigma notation for summations. They are thinking in terms of sets and aggregates. People who write in fixed joins are like people who use a string of plus signs to do a summation. Sometimes you have to use an in fixed operator, but not for inner joins.
The fact that you're asking such an elementary question and that the rest of your code shows me after 30 years that you don't quite get it yet. You don't know that things like "code", "name", and "status" are called attribute properties and are meaningless by themselves. They have to be some kind of code, some kind of status in the name of something in particular. This is a result of the most fundamental principle of logic called the law of identity (to be is to be something in particular; to be nothing in particular or everything in general, is to be nothing at all)
As an old-time programmer I also got a real laugh out of seeing someone the 21st century naming tables as if they were magnetic tape master files. As a complete violation design principle of not mixing data and metadata. You also don't seem to know the status uses a nominal scale and therefore cannot be a numeric value. I'm also trying to figure out why you think "a" and "b" are meaningful, useful names for aliases. The reason people use single letter names as aliases is so it will look like the lettered tape drive names. Basically, this is the way we programmed over 50 years ago.
But perhaps more than that,the idea of having a "code master" or a "name master" makes no sense in RDBMS. We would use DDL and REFERENCES clause for the codes or a "CHECK (foobar_code IN (..))" clause if the list is static and short
Would you explain what it means to truncate a temporal value? You also missed the point that we don't mix data and metadata, so information about updating a table is never stored in the table itself. This is the RDBMS equivalent of keeping a spare set of car keys in the car so that it fits stolen or damaged, you don't have an extra set of keys.
Based on what little I have to go on. I would guess that your code should have looked more like this skeleton:
CREATE ABLE Personnel
(emp_id CHAR(16) NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL,
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),
something_status CHAR(1) NOT NULL
foobar_code CHAR(3) NOT NULL
CHECK (foobar_code LIKE '[a-z][0-9][0-9]');
SELECT job_code, emp_name, COUNT(DISTINCT zip_code) AS zip_cnt
FROM Personnel
WHERE something_status = '1'
AND foobar_code <> 'a10'
GROUP BY job_code, emp_name;
Please post DDL and follow ANSI/ISO standards when asking for help.
March 6, 2020 at 1:57 am
thanks everyone...you all had great answers. equi-join is exactly what I was looking for. I will do more research on it.
FYI, thats not my code. Its actually from techonthenet site.
Jeff - your right its not TSQL...its oracle. i know i know... but I knew it still applied to TSQL. This is a good forum, I knew Id get good answers.
thank you all.
jcelko212: its not my code, although I think you talk out of your ass, I can tell your a smart guy and do appreciate your answers.
March 6, 2020 at 9:12 am
joe - your examples didn't have a join in - they were single table selects
and I have to disagree, the reason we have Inner/outer join syntax makes code more readable and future proof and less likely to make a mistake
MVDBA
March 6, 2020 at 2:43 pm
You also don't seem to know the status uses a nominal scale and therefore cannot be a numeric value.
Status only uses a nominal scale if someone decides to include the status in each record. If you're using foreign keys to a status table, then no, it doesn't. Plus, lots of us receive data where status is numeric, and we have to deal with that.
The reason people use single letter names as aliases is so it will look like the lettered tape drive names. Basically, this is the way we programmed over 50 years ago.
Uh, no, pretty sure NOBODY is thinking about "lettered tape drive names" at all, except for you. They use short aliases because it's convenient. This is a little too short, but it almost definitely had nothing to do with "let's make it look like a tape drive!"
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 9, 2020 at 8:34 pm
>> your examples didn't have a join in - they were single table selects <<
If you've done a good job with your schema, then they will be a lot of single table queries. The results of those queries will be passed on to a presentation layer and used there or the joins that we use over and over will be hidden in VIEWs. If you are competent and have done a good job with your queries, this will occur 70 – 80% of the time in production..
>> and I have to disagree, the reason we have Inner/outer join syntax makes code more readable and future proof and less likely to make a mistake <<
Wrong. I was on the standards committee when he passed the infix join syntax so I think I speak with more authority than you do. Chris Date had done an article on the inner join syntaxes that were available previously. Back in those days, there were two database magazines in the trade, DBMS and Database Programming & Design, his article showed how the use of the Sybase extended the quality (*=) and to other competing syntaxes from Informix and Oracle (+=) operators did not work. I covered some of the differences in my books but basically it's when you apply a filter in the join to a result set.
Chris never submitted any papers to ANSI X3 H2 (the ANSI/ISO standards committee for SQL at the time, where I served for 10 years) and I honestly cannot remember who on the committee did the proposal derived from Date's objections. This paper introduced the concept of preserved and unpreserved tables and gave a whole bunch of extensions to the infix join operator based on that concept. When's the last time you wrote an OUTER UNION? USING? Corresponding? Frankly, this was such a mess that implementers decided not to mess with it.
Dave McGovern was actually right about a committee – it never met a feature, it did not like. 🙁 Once we had the concept of an infix notation with preserved or unpreserved members in the binary operation, we could do all kinds of combinations. If you get the standards you'll see they are actually there.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 9, 2020 at 11:25 pm
>> your examples didn't have a join in - they were single table selects <<
If you've done a good job with your schema, then they will be a lot of single table queries. The results of those queries will be passed on to a presentation layer and used there or the joins that we use over and over will be hidden in VIEWs. If you are competent and have done a good job with your queries, this will occur 70 – 80% of the time in production..
Actually... if you've done a good job with your schema and at least 3rd normal form, single table queries should be the exception (see the next paragraph) rather than the norm. I also can't speak for anyone else but I'll take a stored procedure or an iTVF over a View any day especially if the View contains any aggregates. Oh yeah... and a View is actually just a container for a query and, again, if you've done your job correctly with your schema and 3rd normal form, there's little chance of it containing a single table query.
The only time you might have a lot of single table queries is with basic C.R.U.D.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2020 at 10:01 am
joe - I cannot believe what you just posted.. 3rd normal form is what boyce and codd designed.
if you have a table for customers and they have multiple phone numbers then you need a join
if they live in state/county then you have a join to the country table.
MVDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply