Solved How does "Non-Join" queries work?

[raziel3]

I'm trying to understand how "Non-Join" queries (if that is the proper name for it) work. I know you were taught that queries must be joined on some field to get the results but a query like this confuses me.

Also, I can match the date using "Between" criteria but supposing that I had no ENDDATE and I wanted MTHEND <= Max(STARTDATE) how would I rewrite this query?

 

[isladogs]

A no-join query (AKA Cartesian join or somewhat confusingly 'cross-join') will give a record for every combination of records in each table.
So if you have 50 records in table A and 40 records in table B, the no join query will give 50*40 = 2000 records.

For more info, see my article

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

 

[plog]

Logically, your example is joined. You just didn't do it in the FROM clause but in the WHERE clause.

A true non-join has the fancy name of "cartesian product". The result has [TotalElmentsOfSet1] x [TotalElementsOfSet2] members. All the letters paired with all the digits results in a final set of 260 (a0, b0, c0...x9, y9, z9). In the real world, very infrequently found, however they do have their place.

If you think about it, an INNER JOIN is just criteria between tables. If you look at the SQL of your posted query you will find in there:

WHERE qryELSD.EID = qryTSMTHLY.EID

That could easily be rewritten in the FROM as:

FROM qryELSD INNER JOIN qryELSD ON qryELSD.EID = qryTSMTHLY.EID

In an INNER JOIN you must compare fields of the two datasources, but it doesn't have to be an equals sign that you use. You can use >, <, >=, <=, <>, BETWEEN, LIKE, whatever comparison is available in SQL you can use. That means your MNTHEND criteria can also be moved to the INNER JOIN just like the EID criteria can.

So, your example is not a good one of a non-join. It is a good example of a non-equi join (using other than = in the JOIN).

 

[CJ_London]

they are called 'cartesian queries'. Basically every record in one table will return every record in the other table. So if you have 10 records in one and 100 records in the other you will get 1000 rows back.

For your example you have a match on EID so you can make the join there anyway which will cut down on the number of rows.

Not clear from the second part of your question what the actual requirement is - do you want 'monthend between startdate and enddate OR if enddate is null then monthend<=startdate'? or just 'if enddate is null then monthend<=startdate'

 

[CJ_London]

@isladogs - typo?

if you have 50 records in table A and 30 records in table B, the no join query will give 50*40 = 2000 records.

 

[isladogs]

@isladogs - typo?

Yup. Now fixed. Thanks

 

[raziel3]

Not clear from the second part of your question what the actual requirement is - do you want 'monthend between startdate and enddate OR if enddate is null then monthend<=startdate'? or just 'if enddate is null then monthend<=startdate'

Hey, thanks guys. I beginning to understand.

Can this kind of query work on a table like this?

If MTHEND = 2-Aug-2022 and ERate = 7000, using the MRange Field, would return ENIS = 70.40

or if MTHEND = 8-Aug-2016 and ERate = 7000, using the MRange Field, would return ENIS =64.00

 

[CJ_London]

unable to follow the logic. I don't see what mthend relates to in your sample data. the only date where ENIS=70.40 is 5 sep 2016, nowhere near 2-aug-2022 and the Mrange for that is 6457 and does not =7000. Same situation for ENIS=64. Please show the relevant data or base your example on the example data shown

 

[Pat Hartman]

A query that has no explicit join defined still creates a Cartesian Product as the intermediate result since that is what the query engine has to do first. Multiply table A times table B. Then using the Where clause select the desired records from that. So if we have 40 * 50 = 900 records but only two rows satisfy the Where clause, then ultimately only two rows are returned. But, if you use the join, then the intermediate 900 row recordset does not need to be created so the join will be more efficient. To see if the database engine is using the most efficient method to satisfy the query, you would need to use ShowPlan to see the steps that will be taken in which order.

 

[raziel3]

unable to follow the logic. I don't see what mthend relates to in your sample data. the only date where ENIS=70.40 is 5 sep 2016, nowhere near 2-aug-2022 and the Mrange for that is 6457 and does not =7000. Same situation for ENIS=64. Please show the relevant data or base your example on the example data shown

Ok, having the ENDDATE set that would mean that the date 2-Aug-2022 would fall between 5-Sept-2016 and let's say 31-DEC-9999. So all the rates with the 5-Sept-2016 would be the current. The next parameter would be the ERate. If the ERate is 7000, lookup the max value in MRange that is not more than 7000 and return the ENIS field which would be 70.40.

So the question is can I do Cartesian query to get this?

But I think that, from reading everyone's explations, is to insert a next column for MRange so one would have the upper and other would have the lower and just have a Between criteria on the ERate.

How would you build an expression to get upper and lower ranges without having to insert a whole new column?

 

[plog]

Words aren't working, I think you need to explain with data. Just data.

Provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect your query to produce when you feed it in the data from A.

Again, no more words, just 2 sets of data that show us what you are starting with and what you hope to end up with.

 

[raziel3]

Expected Results

+-----+-----------+-----------+-------+
| EID |  MTHEND   |   ERate   | ENIS  |
+-----+-----------+-----------+-------+
|  39 | 8/31/2022 | $5,000.00 | 53.20 |
|  37 | 7/31/2022 | $7,000.00 | 70.40 |
|  43 | 8/31/2016 | $5,000.00 | 49.40 |
|  40 | 7/31/2015 | $7,000.00 | 64.00 |
+-----+-----------+-----------+-------+

Criteria

+-----------+----------------+
| Parameter |  Lookup Field  |
+-----------+----------------+
| MTHEND    | tblNIS.EffDate |
| ERate     | tblNIS.MRange  |
+-----------+----------------+

Result Field to return -> tblNIS.ENIS

 

[The_Doc_Man]

I am answering the title question:

If you look at the specifications of the SQL language, you realize that SQL engines always start internally with the same thing for a two-table JOIN. They look at every combination of records from table A and from table B. But your starting point is immaterial to you. All you care about is what SQL gives you when it is finished evaluating the SQL statement. There are a few significant cases for this topic.

Case 1: You have a traditional JOIN clause between the two participating tables, specifying equality between two fields, one from each table. That leads to rapid removal of combinations that don't match the JOIN criteria, because the JOIN is enforced early in the process. The next step in SQL processing gets fewer records because of this early filtration.

Case 2: You have no JOIN clause but you do have a WHERE clause that demands equality between two fields, one from each table. In the absence of a JOIN, the SQL processor inherits all of the combinations of records, but using the WHERE clause it filters out combinations that don't match the WHERE criteria. A HAVING clause is applied even later than a WHERE clause because that clause has to wait for aggregates and grouping to have occurred first.

Case 2 is the case about which you originally inquired, a non-JOIN case with WHERE-type filtration.

Case 3: No JOIN and no WHERE and no HAVING clauses. You get a set of records formed by matching each record from table A with each record from table B even if they have nothing in common with each other. This is the cross-product of the two tables and is at least analogous (though not identical) to the cross-product of two vectors to give you a 2-dimensional matrix.

Basically, then, a JOIN clause and a WHERE clause of the type you discussed do the same thing - but at different times in the SQL execution process. Note that this discussion is not merely an academic exercise. Using a JOIN clause makes the subsequent SQL evaluation easier because it has fewer records on which to work. If you have both a JOIN and an arbitrary WHERE clause, particularly if the WHERE clause is complex, the JOIN makes less work for the WHERE clause to take effect. Also, if you have a complex set of computations in the SELECT clause or if you have functions called from the SELECT or WHERE clauses, they should get called less often.

 

[plog]

You give google a starting address and a destination address and it fills in the path between the two. You can't just say "The Eiffel Tower" and expect it to get you there.

A and B. And B must be based on the data in A.

 

[raziel3]

I've attached the db.

qryGROSSWKLY is the Cartesian query.

in Query1, I want to replace the function in the field "NISE" with a Cartesian query.

Hope this helps

 

[raziel3]

So I think I made some headway, thanks to Allen Browne again http://allenbrowne.com/ser-58.html

I restructured tblNIS

SELECT RT.EffDate, Nz((SELECT MIN(RT1.EffDate)
    FROM tblNIS RT1
    WHERE RT1.EffDate> RT.EffDate)-1,#12/31/9999#) AS EndDate, RT.WRange AS LowerWK, (SELECT MIN(RT1.WRange)
    FROM tblNIS RT1
    WHERE RT1.WRange > RT.WRange And  RT1.EffDate = RT.EffDate )-0.01 AS UpperWK, RT.MRange AS LowerMTH, (SELECT MIN(RT1.MRange)
    FROM tblNIS RT1
    WHERE RT1.MRange > RT.MRange And  RT1.EffDate = RT.EffDate )-0.01 AS UpperMTH, RT.ENIS, RT.CNIS, RT.ZNIS, RT.NISID
FROM tblNIS AS RT;

to give me this

then do a Cartesian query

to give me this

Now the question is, would I have issues as my tblNIS grows because it is said that Access's Jet engine cannot handle subqueries too well and I am using 3 of them to make tblNIS query?

 

[The_Doc_Man]

it is said that Access's Jet engine cannot handle subqueries too well

There ARE limits to subqueries with JET or ACE but three of them is not enough to be bad. The limit of queries and nested queries and subqueries is something like 50.