SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

TheFluff posted:

NULL in SQL is quite controversial and has led to a lot of people being very mad at computers, and outer joins result in nulls, so it shouldn't come as a surprise really.

Well, yeah, obviously, there are use cases where an outer join is exactly the right tool for the job and I've explicitly agreed with that above. My entire point is that a lot of the common usage patterns for outer joins (and nullable columns) are footguns or at least footgun-enablers. SQL has no shortage of that sort of thing but nulls in general and things that lead to nulls is one of the most common.

I don't think anyone could? Like, when I wrote "friends don't let friends left join" I thought that was obviously hyperbolic enough to come off as a joke.

I think the thing is, you are saying "only use outer joins when they are the right tool for the job", and most people say "use outer joins unless they are the wrong tool". Like, sure nulls can be weird and sometimes an outer join won't give you what you want, but both are pretty fundamental to how SQL and relational databases work so you shouldn't be afraid to use them as long as you have a handle on the most common gotchas (like filtering on a column from an outer table).

# ? Dec 14, 2020 17:25

Adbot: ADBOT LOVES YOU

# ? Jun 5, 2024 03:52

abelwingnut: Dec 23, 2002

Jethro posted:

you shouldn't be afraid to use them as long as you have a handle on the most common gotchas (like filtering on a column from an outer table).

so the following is taboo:

SELECT * FROM tableA a LEFT JOIN tableB b on a.id = b.idForeign WHERE a.binary = 1

?

# ? Dec 14, 2020 21:50

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

abelwingnut posted:

so the following is taboo:

SELECT * FROM tableA a LEFT JOIN tableB b on a.id = b.idForeign WHERE a.binary = 1

?

This one isn't wrong but, since it's logically equivalent to a plain JOIN, if I saw that in code review I'd flag the line and check if they intended the LEFT to be there and forgot to add "OR a.binary IS NULL" to the filter.

Edit: I'm dumb and tired

NihilCredo fucked around with this message at 07:43 on Dec 15, 2020

# ? Dec 14, 2020 22:11

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

NihilCredo posted:

This one isn't wrong but, since it's logically equivalent to a plain JOIN, if I saw that in code review I'd flag the line and check if they intended the LEFT to be there and forgot to add "OR a.binary IS NULL" to the filter.

What? No it isn't, a is the Left table, b is the table that can be missing and return NULLs. Left/Right Outer has the table specified need to exist and the other side be optional. If it were a FULL OUTER it'd end up being a LEFT because of the non-NULL specified on a. That's a correct join and filter on the driving table.

# ? Dec 15, 2020 00:17

kiwid: Sep 30, 2013

Is there any decent courses out there under $100 that teach the ins and outs of SQL Server Management Studio?

# ? Dec 15, 2020 04:09

abelwingnut: Dec 23, 2002

i�d argue reading up on ssms and putting $100 toward redgate�s sql prompt would go a much longer way. i�d start with the stairway trainings on sqlservercentral.com. i think there�s an ssms one.

# ? Dec 15, 2020 04:33

NPR Journalizard: Feb 14, 2008

Bundy posted:

Please don't give w3schools traffic

why?

# ? Dec 15, 2020 04:35

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Moonwolf posted:

What? No it isn't, a is the Left table, b is the table that can be missing and return NULLs. Left/Right Outer has the table specified need to exist and the other side be optional. If it were a FULL OUTER it'd end up being a LEFT because of the non-NULL specified on a. That's a correct join and filter on the driving table.

Whoops, I misread that as "b.binary = 1". Sorry.

# ? Dec 15, 2020 07:42

abelwingnut: Dec 23, 2002

NihilCredo posted:

Whoops, I misread that as "b.binary = 1". Sorry.

why would b.binary be a problem?

# ? Dec 15, 2020 22:53

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

abelwingnut posted:

why would b.binary be a problem?

because it might be null as a result of the left join, so your where clause would eliminate all such rows, making it behave like an inner join instead. presumably this is not what you wanted, since you wrote "left join"

# ? Dec 15, 2020 23:05

abelwingnut: Dec 23, 2002

DELETE CASCADE posted:

because it might be null as a result of the left join, so your where clause would eliminate all such rows, making it behave like an inner join instead. presumably this is not what you wanted, since you wrote "left join"

yea, i meant a.binary given the post i quoted. but i am not seeing a problem with b.binary either.

in my head, the query planner looks at predicates first. it then alters the tables based on those predicates. finally it joins the altered tables.

so with the b.binary predicate, the query planner reduces tableB to a virtual table with just those rows where binary = 1. it then left joins that to tableA.

i probably have that entirely wrong.

# ? Dec 15, 2020 23:33

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

The planner is both smarter and dumber than you think it is, just always the opposite of whichever you need it to be.

# ? Dec 15, 2020 23:39

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

yeah i can see why you'd think that, but that's not how it works. the joins come first, then the where clause filters the resulting rows. if you want behavior like what you expected, then you have to put the b.binary = 1 condition into the join instead of the where clause. with an inner join, moving the condition around would make no difference. but with an outer join, it does

# ? Dec 15, 2020 23:42

abelwingnut: Dec 23, 2002

so the query planner sees a difference between:

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.idForeign WHERE b.binary = 1

and

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.binary AND b.binary = 1

?

in any case, would it be best to put tableB in a cte with its predicates and LEFT JOIN that?

abelwingnut fucked around with this message at 00:11 on Dec 16, 2020

# ? Dec 16, 2020 00:07

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

abelwingnut posted:

so the query planner sees a difference between:

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.idForeign WHERE b.binary = 1

and

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.binary AND b.binary = 1

?

in any case, would it be best to put tableB in a cte with its predicates and LEFT JOIN that?

Those are different, because your where clause is separate from your joining clauses and filters post join. In most cases it'd be the same and CTEing it wouldn't do anything either way. In all cases, check your explain plans on as live like system as you can, because behaviour can change based on what the db thinks is most effective with CTEs.

# ? Dec 16, 2020 01:48

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

abelwingnut posted:

so the query planner sees a difference between:

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.idForeign WHERE b.binary = 1

and

SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.binary AND b.binary = 1

?

in any case, would it be best to put tableB in a cte with its predicates and LEFT JOIN that?

Unless someone is getting cute with syntax as a stupid joke, the first case forces the join to be an inner join logically.

The second case does not. I'm assuming you typoed the columns and didn't do a.id=b.binary as a dumb gotcha.

Consider the case where a given a.id does not match a b.idForeign:
In snippet 1, that row would have NULL for every column for B. NULL = 1 is FALSE so the WHERE filters out that row of a from the output.

Snippet 2: if there is no matching b.idForeign, the row from a is still returned. b.binary=1 only affects the join predicate.

Snippet 2 also implies that if you have b.idForeign matching an a.id, but b.binary = 0, you wouldn't get that particular row from b, but you would still get the row from a.

E:

TheFluff posted:

NULL in SQL is quite controversial and has led to a lot of people being very mad at computers, and outer joins result in nulls, so it shouldn't come as a surprise really.

I don't think anyone could? Like, when I wrote "friends don't let friends left join" I thought that was obviously hyperbolic enough to come off as a joke.

My initial gut read of the first paragraph was that you're doing a bit. Then I wasn't sure. Your last paragraph tells me I should stick with my instinct.

Nth Doctor fucked around with this message at 01:52 on Dec 16, 2020

# ? Dec 16, 2020 01:49

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I don't think it's incorrect to say that NULL in SQL is controversial. Lots of people have expressed dissatisfaction with NULL. Sometimes that comes from a place of not understanding how NULL works ("I asked for all the rows where X = NULL, why isn't it working?!"), and sometimes it comes from understanding well how it works but disagreeing with it on a philosophical level, thinking it's not the best designed solution for the problem it was supposed to solve.

Indeed, Wikipedia has a page on NULL in SQL and one of the sections is titled "Controversy".

# ? Dec 16, 2020 11:27

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

My opinion is that NULL has a place but actual null values in optional columns should be distinct from the nulls arising from outer joins and other such operations.

Maybe NULL could be grandfathered to indicate either kind of null, and the standards could introduce BUTT and FART keywords for the specific subtypes.

# ? Dec 16, 2020 12:40

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Hammerite posted:

I don't think it's incorrect to say that NULL in SQL is controversial. Lots of people have expressed dissatisfaction with NULL. Sometimes that comes from a place of not understanding how NULL works ("I asked for all the rows where X = NULL, why isn't it working?!"), and sometimes it comes from understanding well how it works but disagreeing with it on a philosophical level, thinking it's not the best designed solution for the problem it was supposed to solve.

Indeed, Wikipedia has a page on NULL in SQL and one of the sections is titled "Controversy".

Huh. TIL.

I could see the difficulties in NULL because not matches vs. NULL because of an unselected option, I suppose but maybe I've been around the concept enough to find it intuitive.

I went to a talk by Pinal Dave in 2014 where he gave an analogy of holding up fingers to the audience. We could of course name the value. Until he put his hand behind his back. Even with a restricted value space, where the number MUST be in the range of 0-5 , we could only represent it as NULL until he showed us. That quantum-superposition-like example was helpful in reinforcing that nothing = NULL because no row has a column that is simultaneously every possible value at once, when a value is set.

# ? Dec 16, 2020 14:10

DrunkMidget: May 29, 2003; 'Shag'd Wo'bram?" -Borra

Spring Heeled Jack posted:

I have a question regarding user logins with Azure SQL and a failover group.

We have a failover group setup and I read that in order for the logins (sql users in this case) to be replicated they need to be contained users. As we already had them created as traditional users from logins that existed on the master, I went through and converted them using the following script: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-migrate-user-to-contained-transact-sql

So now I can see the users showing up on the replicated DBs, but logging in is still not working. I made sure I am specifying the initial database in SSMS, but no go. Is there something else I am missing here? SQL isn�t normally my forte so feel free to give me any suggestions.

I'm not sure if this applies to Azure SQL RDS, but in box product it's a thing: Logins and users are not the same thing. Logins are at the SQL Server level, users are at the database level.

When a login is created it's given a name (ex. "app_user"), but it's also assigned a SID. You can check this out by running:

code:

SELECT * FROM sys.sql_logins

This login can then be given permissions on a database as a user either as a member of a predefined role (db_datareader, etc.) or with a granular set of permissions. When you move or replicate a database to a different server through any means available (AlwaysOn FCI/AG, mirroring, log shipping, backup/restore) then the user and that permission set will come with it. However, if you just create a new login on the new server, even with the exact same name, it will get a different SID and therefore it's technically a different login. MSFT has a handy script available called sp_help_revlogin to copy logins to a new server that will get the same SID and password. Try running this on your working server, grabbing the results and running it on the server that isn't letting you in. https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances

If I've missed the point completely feel free to call me dumb. Also, LEFT JOINs are awesome and good.

# ? Dec 17, 2020 02:33

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Nth Doctor posted:

Huh. TIL.

I could see the difficulties in NULL because not matches vs. NULL because of an unselected option, I suppose but maybe I've been around the concept enough to find it intuitive.

I went to a talk by Pinal Dave in 2014 where he gave an analogy of holding up fingers to the audience. We could of course name the value. Until he put his hand behind his back. Even with a restricted value space, where the number MUST be in the range of 0-5 , we could only represent it as NULL until he showed us. That quantum-superposition-like example was helpful in reinforcing that nothing = NULL because no row has a column that is simultaneously every possible value at once, when a value is set.

That�s a neat and simple way of explaining that concept.

# ? Dec 17, 2020 05:07

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

since every table should have a primary key, which cannot be null, it is easy to distinguish between null values in columns and augmented nulls from outer joins

# ? Dec 17, 2020 07:28

Impotence: Nov 8, 2010; Lipstick Apathy

NPR Journalizard posted:

why?

Often outdated, a disturbing amount of ads and third party tracking. The last time I looked at w3schools (which admittedly has been a while) they were espousing the loveliness of SQLI-vulnerable code, XSS/RFI vulnerable PHP, etc.

At least for web most people use MDN instead.

# ? Dec 17, 2020 12:10

NinpoEspiritoSanto: Oct 22, 2013

NPR Journalizard posted:

why?

Missed this sorry. In addition to the above they're also nothing to do with w3c at all yet trade on the authority of the name.

# ? Dec 17, 2020 12:23

Shy: Mar 20, 2010

What do people replace ssms on macs with?

# ? Dec 18, 2020 11:50

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Shy posted:

What do people replace ssms on macs with?

Dbeaver. Worth installing on Windows as well, it's better than SSMS at more than a few tasks (query management, manual data editing, bulk ETL jobs).

# ? Dec 18, 2020 12:18

Busy Bee: Jul 13, 2004

Basic question - I have a table with customer information on it. I want to find results of multiple distinct transactions where the customer name + transaction amount + zip code all match. I know how to find this information if I'm just looking for that one specific result but if I have many different customer name + amount + zip codes I want to find, how do I write this?

# ? Dec 20, 2020 10:47

Shy: Mar 20, 2010

code:

select distinct customer_name, transaction_amount, zip_code from customer_information

do I understand you correctly?

# ? Dec 20, 2020 10:50

Busy Bee: Jul 13, 2004

Shy posted:

code:

select distinct customer_name, transaction_amount, zip_code from customer_information

do I understand you correctly?

Sorry, I was not clear at all in my original post.

Something like this:

SELECT *
FROM customer_information
WHERE
(customer_name is John and transaction_amount is 30 and zip_code is 12345)
(customer_name is Alex and transaction_amount is 23 and zip_code is 12335)

And then I will get the results of those two WHERE clauses where I can see all the information associated with that distinct customer.

# ? Dec 20, 2020 11:40

Shy: Mar 20, 2010

the same query with corrected syntax

SELECT *
FROM customer_information
WHERE
(customer_name = 'John' AND transaction_amount = 30 AND zip_code = '12345')
OR
(customer_name = 'Alex' AND transaction_amount = 23 AND zip_code = '12335')

# ? Dec 20, 2020 12:02

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Shy posted:

the same query with corrected syntax

SELECT *
FROM customer_information
WHERE
(customer_name = 'John' AND transaction_amount = 30 AND zip_code = '12345')
OR
(customer_name = 'Alex' AND transaction_amount = 23 AND zip_code = '12335')

This, or you could make a temp table and insert the name, amount, zip code combos you're interested in, and join to customer information on all the columns. It will save the query optimizer from potentially rerunning the whole query multiple times.

code:


CREATE TABLE dbo.#Filters (
  name VARCHAR(50)
  amount DECIMAL(9,2),
  zip VARCHAR(10) -- ZIP-ZIP4
);

INSERT dbo.#Filters (
  name,
  amount,
  zip
)
VALUES
  ('john smith', 123.45, '31337'),
  ('john smith', 123.46, '31337'),
  ('joe blow', 456.78, '00001-1234'),
  ('lord haberdasher', 73732.99, '42069-6969');

SELECT *
FROM customer_information AS CI
JOIN dbo.#Filters AS F
  ON F.name = CI.name
  AND F.amount = CI.amount
  AND F.zip = CI.zip;

DROP TABLE dbo.#Filters;

# ? Dec 20, 2020 17:41

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Shy posted:

What do people replace ssms on macs with?

There's also Azure Data Studio for a Microsoft-blessed tool. TW: Electron

# ? Dec 21, 2020 16:01

ConfusedPig: Mar 27, 2013

Can anyone recommend resources on what's the least insanity inducing way to migrate from an Azure SQL database to postgres?

# ? Dec 23, 2020 10:04

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

ConfusedPig posted:

Can anyone recommend resources on what's the least insanity inducing way to migrate from an Azure SQL database to postgres?

I've heard nothing but good things about pgloader.io, although it's more commonly used with MySQL. The site also has a white paper illustrating the envisioned migration process.

Amazon has also recently announced a MSSQL -> Postgres shim so you can use your existing apps while running on Bezos's servers without paying MS for a database license. Obviously jury's still out on it.

# ? Dec 23, 2020 11:21

blue squares: Sep 28, 2007

edit: moved question to different thread

blue squares fucked around with this message at 17:31 on Dec 23, 2020

# ? Dec 23, 2020 16:52

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

NihilCredo posted:

Amazon has also recently announced a MSSQL -> Postgres shim so you can use your existing apps while running on Bezos's servers without paying MS for a database license. Obviously jury's still out on it.

It's this: https://aws.amazon.com/rds/aurora/babelfish/

# ? Dec 31, 2020 03:15

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

In TSQL (MSSQL 2017) How do I select the row name corresponding to the highest (or lowest) value in a column?

I am building an application that monitors AWS CloudFront download times from each of 60 POPs to an EC2 instance in each of the 20 regions, and I would like to optimize it so that it identifies the EC2 instance (and therefore region) with the fastest download time for each POP.

My table looks like this:

code:

                  POP1         POP2         POP3         POP4     ...         POP60
server01          0.2          1.2          5.0          2.3                    0.2
server02          0.1          0.5          4.7          3.7                    0.6
...
server20          0.4          0.7          0.3          4.4                    0.4

I'd like a query that returns MIN(elapsed) and the associated server name for every POP. So in the example, POP1 would return server02, POP2 would return server02,...,POP60 would return server01.

I sorta-kinda have it working in excel using INDEX and MATCH, but I can't figure out a corresponding function in TSQL.

# ? Jan 2, 2021 19:50

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Classic approach for �get me the row that corresponds to the min / max in this column� type problems is select using subquery.

code:

SELECT tbl.server
FROM tbl 
INNER JOIN (
  SELECT MAX(pop1) as M
  FROM tbl
) m
ON tbl.pop1 = m.pop1

Window-function based approaches in SQL 2016+ also work although beware performance issues. The benefit is that you can grab multiple aggregates in a single readable query.

code:

SELECT DISTINCT
  FIRST_VALUE(tbl.server) OVER (ORDER BY tbl.pop1 desc) as pop1,
  FIRST_VALUE(tbl.server) OVER (ORDER BY tbl.pop2 desc) as pop2,
  etc...
FROM tbl

Note that I used FIRST_VALUE. You can use LAST_VALUE but you�ll want to specify the window row range or you�ll get unexpected results.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

Finally, you could also use a windowed max equality check which I wouldn�t recommend but am adding for completeness.

code:

SELECT server
FROM (
  SELECT 
    server,
    pop1,
    MAX(pop1) OVER () as maxPop1
) data
WHERE pop1 = maxPop1

Hope that helps. Phone posting so sorry if there�s anything terribly broken!

# ? Jan 2, 2021 20:40

Empress Brosephine: Mar 31, 2012; by Jeffrey of YOSPOS

hello goons, i got pointed in the direction of using a SQL database for a upcoming project by other goons instead of mongodb. I know the very basics of SQL but im having trouble figuring out how to query and get the details of one table from another table with a reference from a private key. So for example how could i use the following data in a query to get the hometown_location of a minor league baseball teams major league affiliate? my data is structured like this currently:

Table HomeTeam:
team_name varchar(30)
team_id seriel primary key
team_location: varchar(30)

Table MinorLeagueTeam:
minorteam_name varchar(30)
minorteam_location varchar(30)
majorleague_id: reference team_id

I figured out that I can use a Inner Join (?) to get all the data from Table HomeTeam corresponding to minorteam_name = x but I can't figure out how to get all the data from Table HomeTeam and all the data from TableMinorLeague in the same report.

I hope that makes sense

# ? Jan 6, 2021 20:29

Adbot: ADBOT LOVES YOU

# ? Jun 5, 2024 03:52

Shy: Mar 20, 2010

SELECT *
FROM MinorLeagueTeam
INNER JOIN HomeTeam ON MinorLeagueTeam.majorleague_id = HomeTeam.team_id

Your emphasis on "all" is unclear. If you mean there is a one-to-many relationship between the tables, inner join will work as you want anyway, even if the tables are swapped in the query. Joins work by pairing each individual row from table A to each individual row of table B and checking them against a predicate so there is no unintended grouping. For example, if there are 2 major entities and 10 minor entities between them, the predicate will be checked on a set of 2*10=20 rows, and it will be valid on 10 of them, resulting in 10 rows.

Shy fucked around with this message at 23:02 on Jan 6, 2021

# ? Jan 6, 2021 22:51

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »