Re: [sqlite] Conditional JOIN
> Apparently, SQLite always executes the join before evaluating other > conditions. > > Try something like this: > > SELECT * > FROM Big INNER JOIN Small ON Big.ID = Small.BigID > UNION ALL > SELECT *, NULL, NULL, ... > FROM Big > WHERE NOT flag > This is indeed a nice solution! But unfortunately not for my problem :( I am using index to sort rows from Big table (In case of UNION sorting will be too expensive). Seems like the only solution is to use SELECT ..., CASE WHEN Flag (...subselect from Small...) OR NULL END, ... FROM Big ORDER BY sort_idx DESC; Anyway, thank you for help! Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
Pavlo wrote: >> Pavlo wrote: >>> I want to lookup small tables only when flag is set but I can't see >>> how to tell SQLite to skip JOIN lookup for certain rows and return >>> NULLs. >> >> SELECT * FROM Big LEFT JOIN Small ON Big.flag AND Big.ID = Small.BigID > > Sadly, you are wrong here. > Taking your query sqlite will Look for record anyway, and flag 'false' > doesn't stop it! > > Personally I can't understand why it is not so smart to see that > Big.flag does not depend on small table and so when it equivalent to > 'false' there can't be any row in Small to match this condition... Apparently, SQLite always executes the join before evaluating other conditions. Try something like this: SELECT * FROM Big INNER JOIN Small ON Big.ID = Small.BigID UNION ALL SELECT *, NULL, NULL, ... FROM Big WHERE NOT flag Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
Hey Clemens. Thank you for your reply! > Pavlo wrote: > > What I have is one big table storing all mandatory properties of an entity > > and several small tables, storing optional properties. > > > > So, when I need to select big number of elements using JOIN the probability > > of optional properties is low. > > And so, for every row from big table there will be ~99% of join lookups in > > small tables leading to NULLs. > > > > So I got an idea to store flags in big table telling me whether optional > > property is present (flag). > > I want to lookup small tables only when flag is set but I can't see how to > > tell SQLite to skip JOIN lookup for certain rows and return NULLs. > > SELECT * FROM Big LEFT JOIN Small ON Big.flag AND Big.ID = Small.BigID > > However, for small tables, the IDs will be in the cache anyway. > It is unlikely that this will make any noticeable difference. > Sadly, you are wrong here. Taking your query sqlite will Look for record anyway, and flag 'false' doesn't stop it! Personally I can't understand why it is not so smart to see that Big.flag does not depend on small table and so when it equivalent to 'false' there can't be any row in Small to match this condition... To prove I am gonna join Big to itself (to exclude the possibility of neglectable impact on query time of Small): $ time echo "SELECT Big.flags, B.flags X FROM Big LEFT JOIN Big B ON Big.id = B.id;" | sqlite3 database > /dev/null real0m1.885s user0m1.832s sys 0m0.044s $ time echo "SELECT Big.flags, B.flags X FROM Big LEFT JOIN Big B ON Big.flags & 0 AND Big.id = B.id;" | sqlite3 database > /dev/null real0m1.991s user0m1.952s sys 0m0.028s As you can see, second version even bit slower. Extra condition 'Big.flags & 0' is the reason for that... About the cache. It is not a true for us. Our specific project includes the operation with ~million of such databases per server. All data will never fit the FS cache. So we have to fight for as little disc OPs as possible. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
Pavlo wrote: > What I have is one big table storing all mandatory properties of an entity > and several small tables, storing optional properties. > > So, when I need to select big number of elements using JOIN the probability > of optional properties is low. > And so, for every row from big table there will be ~99% of join lookups in > small tables leading to NULLs. > > So I got an idea to store flags in big table telling me whether optional > property is present (flag). > I want to lookup small tables only when flag is set but I can't see how to > tell SQLite to skip JOIN lookup for certain rows and return NULLs. SELECT * FROM Big LEFT JOIN Small ON Big.flag AND Big.ID = Small.BigID However, for small tables, the IDs will be in the cache anyway. It is unlikely that this will make any noticeable difference. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional JOIN
Cheers. I love to use SQLite engine and I am performance paranoid. Today I had an idea how to optimize complex query with JOINs. What I have is one big table storing all mandatory properties of an entity and several small tables, storing optional properties. If for example big table may have 1M records, the small ones will have ~10k of them. So, when I need to select big number of elements using JOIN the probability of optional properties is low. And so, for every row from big table there will be ~99% of join lookups in small tables leading to NULLs. So I got an idea to store flags in big table telling me whether optional property is present (flag). I want to lookup small tables only when flag is set but I can't see how to tell SQLite to skip JOIN lookup for certain rows and return NULLs. I found the way actually: to use CASE + sub-SELECTs instead of JOIN. But if small tables have a list of columns I have to do separate sub-SELECT for each column leading to needless overhead (well, it's still better than original approach). Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
Jay, First, yes I screwed up on the table data examples. The 3/SPECIAL TAbleA values should have shown 2 3/STANDARD TableB entries. My brain is hurting too! Anyway, the main thing is that your latest suggestion works perfectly so thanks for your help, I appreciate it. Pete On Oct 28, 2009, at 5:00 AM, sqlite-users-requ...@sqlite.org wrote: > I think the tail of your TableB data is incorrect. For starters, you > don't have a 3/STANDARD in TableB, or a 3/anything, for that matter. > >> The TableA 2/STANDARD and 3/SPECIAL entries return the correct TABLE >> entries. The TableA entries for 1/SPECIAL and 1/STANDARD return >> duplicated TableB entries. In the case of 1/SPECIAL, the 3 TableB >> entries are returned 3 times for a total of 9 entries. In the case >> of >> 1/STANDARD the 2 TableB entries are returned twice for a total of 4 >> entries. > > Yes-- there was an assumption that (ProdID, PriceTable) was unique > for each table. If you have multiple (ProdID, PriceTable) instances > in TableB (Such as 1/SPECIAL, above), then TableA will join against > each of those. In this case that would/should return 3 rows. > However, > since my solution joins TableB in twice, those 3 rows (from the first > JOIN) are matched again in the second JOIN, producing 3x3=9 results. > You see something similar with 1/STANDARD producing 2x2=4 results. > > If (ProdID, PriceTable) is not unique, then it should return the 3 > rows produced by a single (valid) join. To get rid of the second set > of duplicates (which should only exist if a direct match was found in > the original join) just add an additional condition that > B1.rowid = B2.rowid to force the second set of TableB rows to match > up exactly if (and only if) the B1.rowid is valid and has not already > made the required number of duplicate A1 rows. This basically makes > sure the B2 data is the exact same as the B1 row if a valid B1 row > exists. > > We need to shift the case statement around a bit so it returns the > conditional value itself and not the test value, but consider: > > sqlite> SELECT A1.*, B2.* > ...> FROM TableA AS A1 > ...> LEFT JOIN TableB AS B1 > ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = > B1.PriceTable) > ...> JOIN TableB AS B2 > ...>ON ( A1.ProdID = B2.ProdID AND > ...> CASE B1.PriceTable > ...> WHEN B1.PriceTable THEN > ...> A1.PriceTable = B2.PriceTable AND B1.rowid = B2.rowid > ...> ELSE > ...> 'STANDARD' = B2.PriceTable > ...> END ); > > I think that will do something closer to what you want. > >-j, now my brain hurts > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
On Tue, Oct 27, 2009 at 12:43:56PM -0700, Peter Haworth scratched on the wall: > It almost works but not quite. I believe that at least part of the > reason for this is that, as you commented, there can be multiple > entries in TableB that match any single ProdID/PriceTable in TableA. Then you'll get multiple matches and multiple rows returned for each value. > Here's what I experience. with the following data > > TABLEA.PRODID/PRICETABLE TABLEB.PRODID/PRICETABLE > 1/SPECIAL 1/SPECIAL > 1/SPECIAL > 1/SPECIAL > 1/STANDARD1/STANDARD > 1/STANDARD > 2/STANDARD2/STANDARD > 3/SPECIAL 2/STANDARD > 2/STANDARD I think the tail of your TableB data is incorrect. For starters, you don't have a 3/STANDARD in TableB, or a 3/anything, for that matter. > The TableA 2/STANDARD and 3/SPECIAL entries return the correct TABLE > entries. The TableA entries for 1/SPECIAL and 1/STANDARD return > duplicated TableB entries. In the case of 1/SPECIAL, the 3 TableB > entries are returned 3 times for a total of 9 entries. In the case of > 1/STANDARD the 2 TableB entries are returned twice for a total of 4 > entries. Yes-- there was an assumption that (ProdID, PriceTable) was unique for each table. If you have multiple (ProdID, PriceTable) instances in TableB (Such as 1/SPECIAL, above), then TableA will join against each of those. In this case that would/should return 3 rows. However, since my solution joins TableB in twice, those 3 rows (from the first JOIN) are matched again in the second JOIN, producing 3x3=9 results. You see something similar with 1/STANDARD producing 2x2=4 results. If (ProdID, PriceTable) is not unique, then it should return the 3 rows produced by a single (valid) join. To get rid of the second set of duplicates (which should only exist if a direct match was found in the original join) just add an additional condition that B1.rowid = B2.rowid to force the second set of TableB rows to match up exactly if (and only if) the B1.rowid is valid and has not already made the required number of duplicate A1 rows. This basically makes sure the B2 data is the exact same as the B1 row if a valid B1 row exists. We need to shift the case statement around a bit so it returns the conditional value itself and not the test value, but consider: sqlite> SELECT A1.*, B2.* ...> FROM TableA AS A1 ...> LEFT JOIN TableB AS B1 ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = B1.PriceTable) ...> JOIN TableB AS B2 ...>ON ( A1.ProdID = B2.ProdID AND ...> CASE B1.PriceTable ...> WHEN B1.PriceTable THEN ...> A1.PriceTable = B2.PriceTable AND B1.rowid = B2.rowid ...> ELSE ...> 'STANDARD' = B2.PriceTable ...> END ); I think that will do something closer to what you want. -j, now my brain hurts -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
On Mon, Oct 26, 2009 at 05:32:27PM -0700, Peter Haworth scratched on the wall: > Looking for a way to implement the following situation > > I need to select entries form TableA and TableB. The join needs to > happen using two separate fields. One of this fields can be used in > the normal way but the other filed needs some special logic > > The normal join field is ProdID and the unusual one is PriceTable. If > there are no entries in Table B for the values of ProdID and > ProceTable in the TableA entry then the join must happen using the > value TableA.ProdID and a constant value "STANDARD" to join the > PriceTable entries in TableB to TableA > > Here's an example > > TableA.ProdID TableA.PriceTable TableB.ProdID Tableb.PriceTable > 1 SPECIAL 1 SPECIAL > 1 SPECIAL 1 SPECIAL > 1 STANDARD1 STANDARD > 2 SPECIAL 2 STANDARD > 2 SPECIAL 2 STANDARD > You appear to have duplicate rows in your example, but here are two different ideas. We'll start with this: sqlite> CREATE TABLE TableA ( ProdID, PriceTable ); sqlite> CREATE TABLE TableB ( ProdID, PriceTable ); sqlite> sqlite> INSERT INTO TableA VALUES ( 1, 'STANDARD' ); sqlite> INSERT INTO TableA VALUES ( 1, 'SPECIAL' ); sqlite> INSERT INTO TableA VALUES ( 2, 'SPECIAL' ); sqlite> sqlite> INSERT INTO TableB VALUES ( 1, 'STANDARD' ); sqlite> INSERT INTO TableB VALUES ( 1, 'SPECIAL' ); sqlite> INSERT INTO TableB VALUES ( 2, 'STANDARD' ); First idea: sqlite> SELECT ...>A1.ProdID, ...>A1.PriceTable, ...>coalesce( B1.ProdID, B2.ProdID ), ...>coalesce( B1.PriceTable, B2.PriceTable ) ...> FROM TableA AS A1 ...> LEFT JOIN TableB AS B1 ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = B1.PriceTable ) ...> JOIN TableB AS B2 ...>ON ( A1.ProdID = B2.ProdID and 'STANDARD' = B2.PriceTable ); 1|STANDARD|1|STANDARD 1|SPECIAL|1|SPECIAL 2|SPECIAL|2|STANDARD As I understand it, you're basically trying to match both a ProdID and a PriceTable, but if a PriceTable cannot be found, you want to use the 'STANDARD' PriceTable. This basically JOINs TableA to TableB twice, one with an exact match and one with a 'STANDARD' match. The first match (A1/B1) is done as a LEFT OUTER JOIN, so any mis-matched rows are matched to NULL on the B1 side. The second match (A1/B2) is done as a standard INNER JOIN and assumes there will always be a 'STANDARD' row in TableB for every possible ProdID in TableA. We then use the coalesce() function to return either the valid exact-matched values from B1. If those are NULL, we return the 'STANDARD' match rows from B2. Here is another apprach that uses a CASE statement: sqlite> SELECT A1.*, B2.* ...> FROM TableA AS A1 ...> LEFT JOIN TableB AS B1 ...>ON ( A1.ProdID = B1.ProdID AND A1.PriceTable = B1.PriceTable) ...> JOIN TableB AS B2 ...>ON ( A1.ProdID = B2.ProdID AND B2.PriceTable = ( ...>CASE B1.PriceTable ...>WHEN B1.PriceTable THEN ...> A1.PriceTable ...>ELSE ...> 'STANDARD' ...>END ) ); 1|STANDARD|1|STANDARD 1|SPECIAL|1|SPECIAL 2|SPECIAL|2|STANDARD This is the same basic idea, in that we join TableA to TableB twice. The difference is that we only want the results of the second join (T1/B2) where we join against A1.PriceTable if we know we got a valid match the first time, or 'STANDARD' if we did not. The key to making that work is that ( B1.PriceTable = B1.PriceTable ) will be FALSE (and return the ELSE value) if B1.PriceTable is NULL. I'm sure there are a few other ways you could factor this problem using a double-join, but that's one baisc approach. The heart of the issue is that there is no way to test if a row has a match or not within a single JOIN. You must do the LEFT JOIN to see if there was a match or not. Then, based off the information of that JOIN, you can do the JOIN again and decide to take the values from the first match (if they're valid) or re-try with other values if they are not. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional JOIN
You should be able to do what you want with a single SELECT. You are on the right track with trying to use UNION and EXCEPT. What you are trying to do can be accomplished with a generalization of an outer join. Try something like this pseudo-code, which uses common-table-expressions for readability and to cut redundancy: with MatchA is (select TableA.* from TableA.* inner join TableB), NomatchA is (TableA except MatchA) (select * from MatchA inner join TableB) union (select * from NonmatchA inner join TableC) ... assuming TableC has the values you want to use instead of those from TableB for TableA records that don't match. Or alter the above query to taste for the semantics you actually want. -- Darren Duncan Peter Haworth wrote: > Looking for a way to implement the following situation > > I need to select entries form TableA and TableB. The join needs to > happen using two separate fields. One of this fields can be used in > the normal way but the other filed needs some special logic > > The normal join field is ProdID and the unusual one is PriceTable. If > there are no entries in Table B for the values of ProdID and > ProceTable in the TableA entry then the join must happen using the > value TableA.ProdID and a constant value "STANDARD" to join the > PriceTable entries in TableB to TableA > > Here's an example > > TableA.ProdID TableA.PriceTable TableB.ProdID Tableb.PriceTable > 1 SPECIAL 1 > SPECIAL > 1 SPECIAL 1 > SPECIAL > 1 STANDARD1 > STANDARD > 2 SPECIAL 2 > STANDARD > 2 SPECIAL 2 > STANDARD > > For ProdID 1, the entries in TableB wth ProdID 1 and PriceTable > SPECIAL should be selected. The entry in TableB for ProdID 1 and > PriceTable STANDARD Should NOT be selected > > For ProdID 2, the entries in TableB with ProdID 2 and PriceTable > STANDARD should be selected > > I've tried JOIN with CASE, WHERE with CASE, compound SELECTs with > UNION, UNION ALL, INTERSECT and EXCEPT, but haven't managed to figure > out how to make this work. > > Can this be done in a single SELECT? > > Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional JOIN
Looking for a way to implement the following situation I need to select entries form TableA and TableB. The join needs to happen using two separate fields. One of this fields can be used in the normal way but the other filed needs some special logic The normal join field is ProdID and the unusual one is PriceTable. If there are no entries in Table B for the values of ProdID and ProceTable in the TableA entry then the join must happen using the value TableA.ProdID and a constant value "STANDARD" to join the PriceTable entries in TableB to TableA Here's an example TableA.ProdID TableA.PriceTable TableB.ProdID Tableb.PriceTable 1 SPECIAL 1 SPECIAL 1 SPECIAL 1 SPECIAL 1 STANDARD1 STANDARD 2 SPECIAL 2 STANDARD 2 SPECIAL 2 STANDARD For ProdID 1, the entries in TableB wth ProdID 1 and PriceTable SPECIAL should be selected. The entry in TableB for ProdID 1 and PriceTable STANDARD Should NOT be selected For ProdID 2, the entries in TableB with ProdID 2 and PriceTable STANDARD should be selected I've tried JOIN with CASE, WHERE with CASE, compound SELECTs with UNION, UNION ALL, INTERSECT and EXCEPT, but haven't managed to figure out how to make this work. Can this be done in a single SELECT? Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
