Lines Matching full:join
8961 ** <dd>^SQLITE_STMTSTATUS_FILTER_HIT is the number of times that a join
8964 ** times that the Bloom filter returned a find, and thus the join step
14811 ** tables in a join to 32 instead of 64. But it also reduces the size
17258 #define SQLITE_SimplifyJoin 0x00002000 /* Convert LEFT JOIN to JOIN */
18373 #define EP_OuterON 0x000001 /* Originates in ON/USING clause of outer join */
18374 #define EP_InnerON 0x000002 /* Originates in ON/USING of an inner join */
18575 ** The jointype starts out showing the join type between the current table
18578 ** jointype expresses the join between the table and the previous table.
18601 u8 jointype; /* Type of join between this table and the previous */
18619 Expr *pOn; /* fg.isUsing==0 => The ON clause of a join */
18620 IdList *pUsing; /* fg.isUsing==1 => The USING clause of a join */
18638 Expr *pOn; /* The ON clause of a join */
18639 IdList *pUsing; /* The USING clause of a join */
18658 #define JT_INNER 0x01 /* Any kind of inner or cross join */
18660 #define JT_NATURAL 0x04 /* True for a "natural" join */
18661 #define JT_LEFT 0x08 /* Left outer join */
18662 #define JT_RIGHT 0x10 /* Right outer join */
18664 #define JT_LTORJ 0x40 /* One of the LEFT operands of a RIGHT JOIN
18665 ** Mnemonic: Left Table Of Right Join */
18666 #define JT_ERROR 0x80 /* unknown or unsupported join type */
18689 #define WHERE_RIGHT_JOIN 0x1000 /* Processing a RIGHT JOIN */
19121 u8 withinRJSubrtn; /* Nesting level for RIGHT JOIN body subroutines */
30830 sqlite3_str_appendf(pAccum, "(join-%u)", pSel->selId);
31532 sqlite3_str_appendf(&x, " FULL-OUTER-JOIN");
31534 sqlite3_str_appendf(&x, " LEFT-JOIN");
31536 sqlite3_str_appendf(&x, " RIGHT-JOIN");
31538 sqlite3_str_appendf(&x, " CROSS-JOIN");
100887 ** Join thread pTask->thread.
100920 ** Join all outstanding threads launched by SorterWrite() to create
100931 ** is currently attempt to join one of the other threads. To avoid a race
100932 ** condition where this thread also attempts to join the same object, join
101956 ** lead to the current background thread attempting to join itself.
102386 /* Join all threads */
103859 ** iColumn. Append this new expression term to the FULL JOIN Match set
103927 ExprList *pFJMatch = 0; /* Matches for FULL JOIN .. USING */
103984 ** .... FROM t1 LEFT JOIN (t2 RIGHT JOIN t3 USING(x)) USING(y) ...
104008 /* An INNER or LEFT JOIN. Use the left-most table */
104012 /* A RIGHT JOIN. Use the right-most table */
104017 /* For a FULL JOIN, we must construct a coalesce() func */
104064 /* An INNER or LEFT JOIN. Use the left-most table */
104068 /* A RIGHT JOIN. Use the right-most table */
104073 /* For a FULL JOIN, we must construct a coalesce() func */
104341 ** be multiple matches for a NATURAL LEFT JOIN or a LEFT JOIN USING.
106852 ** Join two expressions using an AND operator. If either expression is
107995 ** the ON or USING clauses of an outer join disqualifies the expression
108094 ** of a LEFT JOIN, and
108128 ** (3) pSrc cannot be part of the left operand for a RIGHT JOIN.
108131 ** (4) If pSrc is the right operand of a LEFT JOIN, then...
108133 (4b) and specifically the ON clause associated with the LEFT JOIN.
108135 ** (5) If pSrc is not the right operand of a LEFT JOIN or the left
108136 ** operand of a RIGHT JOIN, then pExpr must be from the WHERE
109876 /* If the index is on a NULL row due to an outer join, then we
110534 ** that derive from the right-hand table of a LEFT JOIN. The
110537 ** on a LEFT JOIN NULL row.
110561 ** of a LEFT JOIN. */
110967 /* Mark the expression is being from the ON or USING clause of a join
111765 ** This routine is used to check if a LEFT JOIN can be converted into
111766 ** an ordinary JOIN. The p argument is the WHERE clause. If the WHERE
111767 ** clause requires that some column of the right table of the LEFT JOIN
111768 ** be non-NULL, then the LEFT JOIN can be safely converted into an
111769 ** ordinary join.
122197 ** than 64 tables in a join. So any value larger than 64 here
122427 sqlite3ErrorMsg(pParse, "a JOIN clause is required before %s",
122537 ** When building up a FROM clause in the parser, the join operator
122539 ** expects the join operator to be on the right operand. This routine
122540 ** Shifts all join operators from left to right for an entire FROM
122543 ** Example: Suppose the join is like this:
122545 ** A natural cross join B
122547 ** The operator is "natural cross join". The A and B operands are stored
122553 ** * All tables to the left of the right-most RIGHT JOIN are tagged with
122554 ** JT_LTORJ (mnemonic: Left Table Of Right Join) so that the
122556 ** the left operand of at least one RIGHT JOIN.
122568 /* All terms to the left of a RIGHT JOIN should be tagged with the
138403 ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
138404 ** type of join. Return an integer constant that expresses that type
138414 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
138416 ** If an illegal or unsupported join type is seen, then still return
138417 ** a join type, but put an error in the pParse structure.
138419 ** These are the valid join types:
138441 ** of other non-standard and in many cases non-sensical join types.
138442 ** This routine makes as much sense at it can from the nonsense join
138443 ** type and returns a result. Examples of accepted nonsense join types
138446 ** INNER CROSS JOIN -> same as JOIN
138447 ** NATURAL CROSS JOIN -> same as NATURAL JOIN
138448 ** OUTER LEFT JOIN -> same as LEFT JOIN
138449 ** LEFT NATURAL JOIN -> same as NATURAL LEFT JOIN
138450 ** LEFT RIGHT JOIN -> same as FULL JOIN
138451 ** RIGHT OUTER FULL JOIN -> same as FULL JOIN
138452 ** CROSS CROSS CROSS JOIN -> same as JOIN
138454 ** The only restrictions on the join type name are:
138474 u8 code; /* Join type mask */
138512 sqlite3ErrorMsg(pParse, "unknown join type: "
138597 ** the OUTER JOIN processing logic that this term is part of the
138598 ** join restriction specified in the ON or USING clause and not a part
138600 ** WHERE clause during join processing but we need to remember that they
138608 ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
138611 ** term until after the t2 loop of the join. In that way, a
138638 /* Undo the work of sqlite3SetJoinExpr(). This is used when a LEFT JOIN
138639 ** is simplified into an ordinary JOIN, and when an ON expression is
138648 ** if the table that p references is on the left side of a RIGHT JOIN.
138676 ** This routine processes the join information for a SELECT statement.
138678 ** * A NATURAL join is converted into a USING join. After that, we
138689 ** table is the last entry. The join operator is held in the entry to
138690 ** the right. Thus entry 1 contains the join operator for the join between
138691 ** entries 0 and 1. Any ON or USING clauses associated with the join are
138712 /* If this is a NATURAL join, synthesize an approprate USING clause
138718 sqlite3ErrorMsg(pParse, "a NATURAL join may not have "
138760 Expr *pE1; /* Reference to the column on the LEFT of the join */
138761 Expr *pE2; /* Reference to the column on the RIGHT of the join */
138770 sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
138778 ** JOINs. If only a single table on the left side of this join
138781 ** of the join, construct a coalesce() function that gathers all
138787 ** JOIN. But older versions of SQLite do not do that, so we avoid
139567 /* If the UPDATE FROM join is an aggregate that matches no rows, it
141975 ** position in the parent that NULL-able due to an OUTER JOIN. Either the
141976 ** target slot in the parent is the right operand of a LEFT JOIN, or one of
141977 ** the left operands of a RIGHT JOIN. In either case, we need to potentially
141993 ** SELECT a,b,m,x FROM t1 LEFT JOIN (SELECT 59 AS m,x FROM t2) ON b=x;
141995 ** When the subquery on the right side of the LEFT JOIN is flattened, we
142337 ** (2a) the outer query must not be a join and
142343 ** (3) If the subquery is the right operand of a LEFT JOIN then
142344 ** (3a) the subquery may not be a join and
142350 ** See also (26) for restrictions on RIGHT JOIN.
142366 ** (8) If the subquery uses LIMIT then the outer query may not be a join.
142401 ** (17f) the subquery must not be the RHS of a LEFT JOIN.
142454 ** (26) The subquery may not be the right operand of a RIGHT JOIN.
142455 ** See also (3) for restrictions on LEFT JOIN.
142457 ** (27) The subquery may not contain a FULL or RIGHT JOIN unless it
142460 ** (27b) the subquery is a compound query and the RIGHT JOIN occurs
142490 int isOuterJoin = 0; /* True if pSub is the right side of a LEFT JOIN */
142544 ** If the subquery is the right operand of a LEFT JOIN, then the
142545 ** subquery may not be a join itself (3a). Example of why this is not
142548 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
142552 ** (t1 LEFT OUTER JOIN t2) JOIN t3
142607 ** omitted on left-hand tables of the right join that is being
143099 ** part of a ON clause from a LEFT JOIN, then throughout the query
143164 ** RIGHT JOIN anywhere in the query */
143167 /* Do not propagate constants through the ON clause of a LEFT JOIN */
143246 ** (4) The inner query is the right operand of a LEFT JOIN and the
143248 ** on that LEFT JOIN.
143251 ** of a LEFT JOIN where iCursor is not the right-hand table of that
143252 ** left join. An example:
143256 ** JOIN (SELECT 1 AS b2 UNION ALL SELECT 2) AS bb ON (a1=b2)
143257 ** LEFT JOIN (SELECT 8 AS c3 UNION ALL SELECT 9) AS cc ON (b2=2);
144240 /* In a join with a USING clause, omit columns in the
144725 ** Check to see if the pThis entry of pTabList is a self-join of a prior view.
145023 ** reduction of join operators) in the FROM clause up into the main query
145036 /* Convert LEFT JOIN into JOIN if there are terms of the right table
145037 ** of the LEFT JOIN used in the WHERE clause.
145044 ("LEFT-JOIN simplifies to JOIN on term %d\n",i));
145065 ** is not a join. But if the outer query is not a join, then the subquery
145089 ** (b) The subquery is part of a join
145165 ** a join. No need to speed time on this operation for non-join queries
145276 ** (3) the subquery is not part of a left operand for a RIGHT JOIN
151354 ** Extra information attached to a WhereLevel that is a RIGHT JOIN.
151380 int iLeftJoin; /* Memory cell used to implement LEFT OUTER JOIN */
151396 WhereRightJoin *pRJ; /* Extra information for RIGHT JOIN */
151422 ** term of a join. Every term of the FROM clause will have at least
151569 ** The number of terms in a join is limited by the number of bits
151747 ** clause is processed, so that every table in a join is guaranteed to be
151749 ** baseline limit was exhausted by prior tables of the join.
151770 SrcList *pTabList; /* List of tables in the join */
152134 sqlite3_str_appendf(&str, " LEFT-JOIN");
152235 ** if it controls a LEFT OUTER JOIN and it did not originate in the ON
152236 ** or USING clause of that join.
152240 ** (1) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
152241 ** (2) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
152246 ** of a LEFT OUTER JOIN. In (1), the term is not disabled.
152249 ** of the join. Disabling is an optimization. When terms are satisfied
152867 ** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the
152978 ** JOIN for which the current table is not the rhs are omitted
152981 ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms
152985 ** SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
153304 /* If this is the right table of a LEFT OUTER JOIN, allocate and
153306 ** row of the left table of the join.
153314 VdbeComment((v, "init LEFT JOIN no-match flag"));
153978 ** for a LEFT JOIN.
153987 ** a LEFT JOIN: */
154105 ** is required in a few obscure LEFT JOIN cases where control jumps
154132 ** ON or USING clause of a LEFT JOIN, and terms that are usable as
154136 ** is not contained in the ON clause of a LEFT JOIN.
154403 ** join processing. tag-20220513a */
154505 /* For a RIGHT OUTER JOIN, record the fact that the current row has
154515 /* pTab is the right-hand table of the RIGHT JOIN. Generate code that
154546 /* For a LEFT OUTER JOIN, generate code that will record the fact that
154552 VdbeComment((v, "record LEFT JOIN hit"));
154560 ** of the RIGHT JOIN. During normal operation, the subroutine will
154571 /* WHERE clause constraints must be deferred until after outer join
154573 ** to the results of the OUTER JOIN. The following loop generates the
154608 ** for a RIGHT JOIN.
154627 ExplainQueryPlan((pParse, 1, "RIGHT-JOIN %s", pTabItem->pTab->zName));
155156 ** a join, then transfer the appropriate markings over to derived.
155601 ** 3. Not originating in the ON clause of an OUTER JOIN
155756 Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */
155813 ** on left table of a LEFT JOIN. Ticket #3015 */
155819 /* The ON clause of an INNER JOIN references a table to its right.
155823 ** if there is a RIGHT or FULL JOIN in the query. This makes SQLite
156194 /* Prevent ON clause terms of a LEFT JOIN from being used to drive
156195 ** an index for tables to the left of the join.
157297 ** We know that pSrc is an operand of an outer join. Return true if
157298 ** pTerm is a constraint that is compatible with that join.
157301 ** outer join. pTerm can be either EP_OuterON or EP_InnerON if pSrc
157302 ** is the left operand of a RIGHT join.
157306 ** the right table of a RIGHT JOIN because the constraint implies a
157307 ** not-NULL condition on the left table of the RIGHT JOIN.
157342 const Bitmask notReady /* Tables in outer loops of the join */
157417 ** WHERE clause (or the ON clause of a LEFT join) that constrain which
159202 ** operand, or if the loop is not an OUTER JOIN.
159771 u8 jointype, /* The JT_* flags on the join */
159889 ** Add all WhereLoop objects for a single table of the join where the table
159994 && (pSrc->fg.jointype & JT_RIGHT)==0 /* Not the right tab of a RIGHT JOIN */
160160 /* Do not do an SCAN of a index-on-expression in a RIGHT JOIN
160162 ** positioned to the correct row during the right-join no-match
160521 ** Add all WhereLoop objects for a table of the join identified by
160524 ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and
160527 ** separated from it by at least one LEFT or CROSS JOIN. Similarly, the
160530 ** CROSS JOIN.
160534 ** ... FROM t1, t2 LEFT JOIN t3, t4, vt CROSS JOIN t5, t6;
160699 /* The multi-index OR optimization does not work for RIGHT and FULL JOIN */
160820 /* Loop over the tables in the join, from left to right */
160840 ** prevents the right operand of a RIGHT JOIN from being swapped with
160845 ** a LEFT JOIN over to the left side of that join if the LEFT JOIN
160846 ** is itself on the left side of a RIGHT JOIN.
161331 int nLoop; /* Number of terms in the join */
161333 int iLoop; /* Loop counter over the terms of the join */
161868 /* Attempt to omit tables from a join that do not affect the result.
161872 ** 2) The table must be the RHS of a LEFT JOIN.
161888 ** LEFT JOIN t2 ON (t1.ipk=t2.ipk)
161889 ** LEFT JOIN t3 ON (t1.ipk=t3.ipk)
161894 ** LEFT JOIN t2
161895 ** LEFT JOIN t3 ON (t1.ipk=t3.ipk)
162126 ** entire tables. Thus a three-way join is an O(N^3) operation. But if
162141 ** An outer join of tables t1 and t2 is conceptally coded as follows:
162218 sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
162298 ** Knowing the bitmask for all tables to the left of a left join is
162330 /* Special case: WHERE terms that do not refer to any tables in the join
162458 /* Attempt to omit tables from a join that do not affect the result.
162694 /* The nature of RIGHT JOIN processing is such that it messes up
162696 ** optimizations. We need to do an actual sort for RIGHT JOIN. */
162824 ** the RIGHT JOIN table */
162892 /* For LEFT JOIN queries, cursor pIn->iCur may not have been
162982 /* Do RIGHT JOIN processing. Generate code that will output the
162983 ** unmatched rows of the right operand of the RIGHT JOIN with
167597 0, /* JOIN => nothing */
167870 /* 143 */ "JOIN",
168175 /* 122 */ "joinop ::= COMMA|JOIN",
168176 /* 123 */ "joinop ::= JOIN_KW JOIN",
168177 /* 124 */ "joinop ::= JOIN_KW nm JOIN",
168178 /* 125 */ "joinop ::= JOIN_KW nm nm JOIN",
169086 258, /* (122) joinop ::= COMMA|JOIN */
169087 258, /* (123) joinop ::= JOIN_KW JOIN */
169088 258, /* (124) joinop ::= JOIN_KW nm JOIN */
169089 258, /* (125) joinop ::= JOIN_KW nm nm JOIN */
169496 -1, /* (122) joinop ::= COMMA|JOIN */
169497 -2, /* (123) joinop ::= JOIN_KW JOIN */
169498 -3, /* (124) joinop ::= JOIN_KW nm JOIN */
169499 -4, /* (125) joinop ::= JOIN_KW nm nm JOIN */
170349 case 122: /* joinop ::= COMMA|JOIN */
170352 case 123: /* joinop ::= JOIN_KW JOIN */
170355 case 124: /* joinop ::= JOIN_KW nm JOIN */
170358 case 125: /* joinop ::= JOIN_KW nm nm JOIN */
172092 ** 103: JOIN ISNULL OFFSET
172223 testcase( i==82 ); /* JOIN */
214412 /* Force DBSTAT table should always be the right-most table in a join */