The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,532 other followers

Archive for the ‘PostgreSQL’ Category

What RDBMS do you use when doing location-aware things? 

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 »

SQL: “where not exists … having” formulation; anti-join alternative

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:

A search on SO for code:"EXISTS (SELECT 1" yields 5,048 results.
A search on SO for code:"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 find SELECT 1 more intuitive. It’s like saying “if at least one exists”.
Is SELECT * more intuitive?

–jeroen

 

 

Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »

The ##Netherlands saw a big I.T. project meant to deliver a new citizen regis…

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 »

Visual Representation of SQL Joins – CodeProject

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:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN
  • LEFT JOIN EXCLUDING INNER JOIN
  • RIGHT JOIN EXCLUDING INNER JOIN
  • OUTER JOIN EXCLUDING INNER JOIN

Note:

  • the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
  • the opposite of OUTER JOIN is empty set.

But the diagram is usually speaks for itself.

–jeroen

Source: Visual Representation of SQL Joins – CodeProject

Read the rest of this entry »

Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »

Venn Diagrams of SQL Join queries (via: Data Visualization – Google+)

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 »

 
%d bloggers like this: