Hot answers tagged

12

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join: SELECT * FROM user_branch T1 LEFT JOIN dimcustomer2 T2 ON T1.BRANCH_CODE = T2.BRANCH_CODE INNER JOIN customer_guarantee T3 ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM Let us assume that some rows in T1 have no matches in T2. More ...


10

I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning. Joined ...


4

A FULL OUTER JOIN would be one way: create table table1 (column1 int, column2 int null) insert into table1 values (1,1), (2,3), (3,2), (10,12), (5,10) select * from table1 t1 full outer join table1 t2 on t1.column1 = t2.column2 where t1.column1 is null or t2.column2 is null Or wrap that in EXISTS to get it as a single set. select x.* from table1 x where ...


4

Simple addition and subtraction. Although the code is SQL code, the math works the same in PL/SQL. This code: alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; with data as ( select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual ) select ...


4

Your particular example uses and inner join. The results will always be the same whether you use ON or WHERE. I think of the ON clause as applying the predicate to the row from the right-hand table before it is combined with the left-hand row. The WHERE applies after the combination is formed. This is just how I think inside my head. The query optimizer ...


3

All the JOINs actually belong to the FROM clause. Semantically, it does not make a difference in which order the JOINs are written, as long as you maintain the ON clauses and don't use LEFT/RIGHT OUTER JOIN clauses. Said differently, the output of the FROM and the JOIN clauses is a single large relation where it clearly does not really matter in which order ...


3

Assuming no NULL values in any field of any record, and no duplicates by (brand, origin, destination) in any separate table... Core query: SELECT Brand, Origin, Destination, Price, MAX(CASE WHEN source = 1 THEN price END) price1, MAX(CASE WHEN source = 2 THEN price END) price2 FROM ( SELECT Brand, Origin,...


3

Disclaimer, everything is nullable in your tables so IS NULL predicates may give false positives. Your query comes a long way, all you need is to add certain predicates: 1 and 2. SELECT Table1.Brand , Table1.Origin, Table1.Destination, Table1.Price, Table2.Price, Table1.Price - Table2.Price as diff FROM Table1 ...


2

One way to do it is with this procedure: { # "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $" # # Stored procedure FROM_UNIX_TIME written by Jonathan Leffler # (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME. # # If you run this procedure with no arguments (use the default), you # need to worry about the time zone the database ...


2

Test select: select macaddress, upper(replace(macaddress,':','')) as new_macaddress from Macs; Update: update macs set macaddress = upper(replace(macaddress,':','')); Fiddle Test data: create table macs ( macaddress varchar(20) ); insert into macs values('90CCAADD3341'); insert into macs values('90:3f:ff:11:22:33'); insert into macs values('33:44:...


2

SELECT shop_id, COUNT(DISTINCT card_id) number_of_expired_cards FROM sourcetable WHERE card_expires < GETDATE() GROUP BY shop_id


2

Will there be any condition that these two queries produce different result set? Logically, both queries must produce same result set, but deepening on Query Optimizer and Logical Processing Order both queries may produce different work-load (CPU Worker time, Logical Reads etc..) due to following condition: Query 1: Retrieves the filtered rows based on ...


2

A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as ...


2

The columns in common, do not have to have the same name, they just need to have the same data. I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, ...


2

I don't think that it's failing to use the index, but that it has a lot to do with a combination of factors: number of rows in the table number of unique values in col1 query planner tuning (seq_page_cost,random_page_cost,etc.) It could very well be that the lowest-cost plan involves a Sequential Scan, at least for this situation. On my machine, I get the ...


1

First issue that I have noticed in the provided plan is --> There are so many implicit conversions as listed below: <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/> <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="...


1

I've used this data as start point: create table t ( target_link_id int, reversed_target_link boolean, source_geometry_id int, reliability double precision ); insert into t values (750, true, 6156655, 96.90882243480968), (750, true, 6156654, 96.3385845004076), (759, false, 299963, 97.88820671302537), (759, false, 299962, 90....


1

A big problem if you don't have any monitoring set up is that your cached data wont stay forever. Which means you can't go as far back as you want. The only way to reliably query this (in the future that is) is to set up some monitoring that captures queries that are being fired on your server. To monitor a server I often setup a really easy WhoIsActive ...


1

Generic code for calculating 1,2,3,4,..6,...12 years / quarters / months / weeks / days /hours moving average, median, percentiles, etc. summary stats where table contains a list of individual time records (like sales transactions,etc) WITH grid AS ( SELECT end_time, start_time FROM ( SELECT end_time , lag(end_time, 12, '...


1

It sounds like you're making some headway Tom. As you've no doubt realised from your work and the answers provided, joins in SQL are for matching on common data between tables. SQL server doesn't actually prevent you from joining on totally unrelated data, the only thing it enforces is that the type of data is the same between columns you are matching on....


Only top voted, non community-wiki answers of a minimum length are eligible