Posted by jpluimers on 2019/01/08
There was a nice discussion on [WayBack] What RDBMS do you use when doing location-aware things?More specifically, I am looking into a simple app with use of GPS… – Andrea Raimondi – Google+
TL;DR: use PostGIS on top of PostgresSQL.
–jeroen
Posted in Database Development, Development, PostGIS | Leave a Comment »
Posted by jpluimers on 2018/06/26
I need to write up some notes, but there are some links that will help me:
It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id); SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);I am not considering NULL or “fun variants” which don’t seem intuitive to me.
SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);
The question popped up in comments just now. I researched the manuals of the most popular RDBMS:
- MS SQL seems to favor
SELECT *in the manual.- The example in the PostgreSQL 9.4 manual uses
SELECT 1.- Oracle 11g has
SELECT *in the language reference.- MySQL 5.7 has
SELECT *in the reference manual but alsoSELECT 1in the comments.- SQLite has no example in the language reference.
A search on SO for
code:"EXISTS (SELECT 1"yields 5,048 results.
A search on SO forcode:"EXISTS (SELECT *"yields 5,154 results.
Updated links and counts 07.2015.So
SELECT *has the popular vote and the big commercial RDBMS on its side.
I findSELECT 1more intuitive. It’s like saying “if at least one exists”.
IsSELECT *more intuitive?
–jeroen
Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2017/11/30
Interesting: https://github.com/MinBZK/OperatieBRP
[WayBack] The Netherlands saw a big I.T. project meant to deliver a new citizen registry system fail. So they did one thing right. All the source code is now on github… – Jan Wildeboer – Google+
It includes both documentation and source code.
A bit of browsing reveals at least some of the technologies used:
Most of the SQL code seems to be generated.
There are truckloads of .XML configuration files, typical for a Java environment. Some configuration is in .properties files.
–jeroen
Posted in ActiveMQ, Development, Java, Java Platform, MQ Message Queueing/Queuing, Open Source, PostgreSQL, Software Development | Leave a Comment »
Posted by jpluimers on 2017/08/02
I thought I posted a reference to this a long time ago, but didn’t.
It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:
The article explains these in greater detail:
Note:
But the diagram is usually speaks for itself.
–jeroen
Source: Visual Representation of SQL Joins – CodeProject
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2014/04/08
Some great venn diagrams of SQL JOINs via Data Visualization – Google+ of which I wish they were PNG:
Thanks to that link, I found the original article and images by C.L. Moffat!
Click on the image to view a larger version.
–jeroen Read the rest of this entry »
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
