dbjoin - join two tables on common columns
dbjoin [-Sid] --input table1.fsdb --input table2.fsdb [-nNrR] column [column...]
cat table1.fsdb | dbjoin [-Sid] --input table2.fsdb [-nNrR] column [column...]
Does a natural, inner join on TABLE1 and TABLE2 the specified columns. With the
-a option, or with
-t outer it will do a natural, full outer join.
(Database review: inner joints output records only when there are matches in both tables and will omit records that do not match. Outer joins output all records from both tables, filling with the empty value as needed.)
By default, data will be sorted lexically, but the usual sorting options can be mixed with the column specification.
Because two tables are required, input is typically in files. Standard input is accessible by the file "-".
If data is already sorted, dbjoin will run more efficiently with the
The resource requirements dbjoin vary. If input data is sorted and
-S is given, then memory consumption is bounded by the 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.
One 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 warning "lots of matching rows accumulating in memory" appears, this is the cause and try swapping join order.
Perform a full outer join, include non-matches (each record which doesn't match at all will appear once). Default is an inner join.
Explicitly specify the join type. TYPE must be inner, outer, left, or right. Currently only inner and outer are implemented.
assume (and verify) data is already sorted
give value E as the value for empty (null) records
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):
sort in reverse order (high to low)
sort in normal order (low to high)
This module also supports the standard fsdb options:
Enable debugging output.
Read from InputSource, typically a file name, or
- for standard input, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
Write to OutputDestination, typically a file name, or
- for standard output, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
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.
Show full manual.
#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
cat DATA/reg.fsdb | dbsort -n cid | dbjoin -i - -i DATA/classes -n cid
#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
$filter = new Fsdb::Filter::dbjoin(@arguments);
Create a new dbjoin object, taking command-line arguments.
Internal: set up defaults.
Internal: parse command-line arguments.
Internal: setup, parse headers.
Internal: run over each rows.
Copyright (C) 1991-2008 by John Heidemann <firstname.lastname@example.org>
This program is distributed under terms of the GNU general public license, version 2. See the file COPYING with the distribution for details.