Navicat Blog

Seamless MySQL and MariaDB Management with Navicat On-Prem Server Oct 28, 2024 by Robert Gravelle

Navicat Collaboration provides the means for your team to collaborate on a variety of database objects, including connection settings, queries, aggregation pipelines, snippets, model workspaces, BI workspaces and virtual group information. Navicat offers two options for Collaboration: Navicat Cloud and Navicat On-Prem Server. Whereas Navicat Cloud offers a central space for your team to store Navicat objects, Navicat On-Prem Server is an on-premise solution for hosting a cloud environment where you can securely store Navicat objects internally at your location. Today's blog will describe how Navicat On-Prem Server helps foster collaboration within your team and manage MySQL and MariaDB instances more effectively.

The SQL Anti Join Oct 21, 2024 by Robert Gravelle

One of the most powerful SQL features is the JOIN operation, providing an elegant and simple means of combining every row from one table with every row from another table. However, there are times that we may want to find values from one table that are NOT present in another table. As we'll see in today's blog article, joins can be utilized for this purpose as well, by including a predicate on which to join the tables. Known as anti joins, these can be helpful in answering a variety of business-related questions, such as:

  • Which customers did not place an order?
  • Which employees have not been assigned a department?
  • Which salespeople did not close a deal this week?

This blog will offer a primer on the types of anti joins and how to write them using a few examples based on the PostgreSQL dvdrental database. We'll write and execute the queries in Navicat Premium Lite 17.

The SQL Semi Join Oct 15, 2024 by Robert Gravelle

Most database developers and administrators are familiar with the standard inner, outer, left, and right JOIN types. While these can be written using ANSI SQL, there are other types of joins that are based on relational algebra operators that don't have a syntax representation in SQL. Today we'll be looking at one such join type: the Semi Join. Next week we'll tackle the similar Anti Join. To gain a better understanding of how these types of joins work, we'll execute some SELECT queries in Navicat Premium Lite 17 against the PostgreSQL dvdrental database. It's a free database that's based on the MySQL Sakila Sample Database.

Filtering Aggregated Fields Using the Having Clause Oct 8, 2024 by Robert Gravelle

If you have been writing SQL queries for some time, you are probably quite familiar with the WHERE clause. While it has no effect on aggregated fields, there is a way to filter records according to aggregate values, and that is by using the HAVING clause. This blog will cover how it works as well as provide a few examples on using it in SELECT queries.

Writing SELECT Queries with EXISTS Sep 26, 2024 by Robert Gravelle

The SQL EXISTS operator offers us an easy way to retrieve data based on the existence (or non-existence) of some other data. More specifically, it's a logical operator that evaluates the results of a subquery and returns a boolean value indicating whether rows were returned or not. While the IN operator can be utilized for much the same purpose, there are some differences to be aware of. Today's blog will cover how to use the EXISTS operator using a few examples as well as provide some guidance as to when to use EXISTS rather than IN.

Navicat Blogs
Feed Entries
Share
Blog Archives