DBJOIN(1)

📦
version 3.9 · 3 versions
, in epel 9

👑 DBJOIN(1) — the dbjoin manual, bestie edition 👑

okay so welcome to dbjoin, the Fsdb tool that joins two tables on shared columns. this is the DBJOIN(1) page, user-contributed Perl documentation, and it's genuinely one of the most useful little icons in the whole suite. let's get into it 💅

✨ NAME ✨

dbjoin — join two tables on common columns 🫶

🌸 SYNOPSIS 🌸

dbjoin [-Sid] --input table1.fsdb --input table2.fsdb [-nNrR] column [column...]

OR (the "pipe it in" era) 👇

cat table1.fsdb  | dbjoin [-Sid] --input table2.fsdb [-nNrR] column [column...]

🦋 DESCRIPTION 🦋

So here's the vibe: dbjoin does a natural, inner join on TABLE1 and TABLE2 across the columns you name. that's the default, no cap.

With the -a option, or with -t outer, it'll do a natural, full outer join instead. glow-up achieved ✨

database review, bestie (this is the lore you need): inner joins output records only when there are matches in both tables, and they will omit records that do not match. outer joins output all records from both tables, filling with the empty value as needed. right (left) outer joins keep all elements of the right (left) table, even those that don't match in the other table. slay, that's the whole taxonomy 💀

By default, for non-hash joins, data will be sorted lexically, but the usual sorting options can be mixed in with the column specification. it's giving flexible ✨

Because two tables are required, input is typically in files. standard input is accessible by the file -.

If only one input is given, the first (left) input is taken from stdin. iconic behavior honestly 🌈

🔥 RESOURCE REQUIREMENTS AND PERFORMANCE 🔥

Joins can be expensive. most databases have a query optimizer that knows something about the data and so can select algorithms for efficient operation — in Fsdb, you are that optimizer. yes, main character energy, that's you 👑

For non-hash joins: if data is already sorted, dbjoin will run more efficiently by telling dbjoin the data is sorted with the -S.

The resource requirements of dbjoin vary:

  • If input data is sorted and -S is given, then memory consumption is bounded by the sum of the largest number of records in either dataset with the same value in the join column, and there is no disk consumption ✨
  • If data is not sorted, then dbjoin requires disk storage the size of both input files 😭

You can minimize memory consumption by making sure each record of table1 matches relatively few records in table2. typically this means that table2 should be the smaller. for example, given two files: people.fsdb (schema: name iso_country_code) and countries.fsdb (schema: iso_country_code full_country_name), then

dbjoin -i people.fsdb -i countries.fsdb iso_country_code

will require less memory than

dbjoin -i countries.fsdb -i people.fsdb iso_country_code

if there are many people per country (as one would expect). if you see the warning "lots of matching rows accumulating in memory" appear, this is the cause — try swapping join order. consider it a little nudge from the universe 🧚

For hash joins (that is, with -m righthash or -m lefthash): all of the right table (the second input) or the left (the first) is loaded into memory (and "hashed"). the other table need not be sorted. Runtime is O(n), but memory is O(size of hashed table). trade-offs, bestie, trade-offs 💅

💖 OPTIONS 💖

  • -a, --all — perform a full outer join, include non-matches (each record which doesn't match at all will appear once). Default is an inner join.
  • -t TYPE, --type TYPE — explicitly specify the join type. TYPE must be inner, outer, left (outer), right (outer). (recall that inner join requires data on both sides, outer joins keep all records from both sides for outer, or all of the first or second input for left and right outer joins.) Default: inner.
  • -m METHOD, --method METHOD — select join method (algorithm). choices are merge, righthash, and lefthash. with hashes, that side is cached. ⚠️ Warning: the lefthash method is about caching the left input, and it does not imply a left-type join, which keeps even unmatched left keys. do not confuse these, i'm begging 🙈 Default: merge.
  • -S, --pre-sorted — assume (and verify) data is already sorted.
  • -e E, --empty E — give value E as the value for empty (null) records.
  • -T TmpDir — where to put tmp files. also uses environment variable TMPDIR, if -T is not specified. Default is /tmp.

🎀 Sort specification options 🎀

(can be interspersed with column names — mix and match, it's a vibe ✨)

  • -r, --descending — sort in reverse order (high to low)
  • -R, --ascending — sort in normal order (low to high)
  • -n, --numeric — sort numerically
  • -N, --lexical — sort lexicographically

⭐ Standard fsdb options ⭐

this module also supports the standard fsdb options, bestie:

  • -d — enable debugging output.
  • -i InputSource, --input InputSource — read from InputSource, typically a file name, or - for standard input, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
  • -o OutputDestination, --output OutputDestination — write to OutputDestination, typically a file name, or - for standard output, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
  • --autorun, --noautorun — by default, programs process automatically, but Fsdb::Filter objects in Perl do not run until you invoke the run() method. the --(no)autorun option controls that behavior within Perl.
  • --help — show help.
  • --man — show full manual.

🍓 SAMPLE USAGE 🍓

Input:

#fsdb sid cid
1 10
2 11
1 12
2 12

And in the file DATA/classes:

#fsdb cid cname
10 pascal
11 numanal
12 os

Command:

cat DATA/reg.fsdb | dbsort -n cid | dbjoin -i - -i DATA/classes -n cid

Output:

#fsdb      cid     sid     cname
10      1       pascal
11      2       numanal
12      1       os
12      2       os
# - COMMENTS:
#  | /home/johnh/BIN/DB/dbsort -n cid
# DATA/classes COMMENTS:
# joined comments:
#  | /home/johnh/BIN/DB/dbjoin - DATA/classes cid

🌈 SEE ALSO 🌈

Fsdb. (go read it, it's the whole ecosystem 💜)

Copyright (C) 1991-2022 by John Heidemann <johnh@isi.edu>

This program is distributed under terms of the GNU general public license, version 2. see the file COPYING with the distribution for details.

okay that's the whole page, bestie, you're officially an Fsdb join expert 🎀 this was perl v5.32.1, dated 2024-08-02, and it's the DBJOIN(1) page signing off — now go join some tables, you absolute icon 💖✨🦋

📦 where this page comes from